Hello, in this tutorial let us show you how to transform time into numbers, so you can calculate hours worked in Excel
Let's say that you are a contractor, a freelancer or you just need to keep track of your hours in your current job
Look at this example, here we have in this spreadsheet a sample for a week, the dates are in the first column, then the time in and out, the total hours for that day, and to make it more realistic, we also added columns for the rate and total amounts
What you see now is an unfinished version so we can walk you through each step.
First, make sure that you enter the time in and out by typing the number, space and then enter am or pm.
Look at what happens if we don't enter the space, Excel will not see it as a time format but instead a text, we don't want that, so we add the space, and now Excel reads the time format as we want
Next, we want to modify the times because they also show the seconds, this is not needed for us. Try not to skip this important step.
To fix them first select the group of time in and out, press the Home Tab and in the Number, group select more number formats. I am using Excel for Office 365 so if you are using an older version try choosing the very last option at the bottom of that list.
The new options to format the time are here. We will select the third option from top to bottom, but there are other options available if you wish to use them instead.
Now the hours look good, and this is the part where we start getting deeper into how Excel calculates the time.
First, let's subtract the time in and out in Excel to get the difference of hours worked. Type equal and select the time out minus the time in.
If you do it backwards or subtract time in minus out, you will get a negative and incorrect result. I am turning them into a number, so you see what Excel is reading. Let's delete the minus result since we know it is incorrect.
Delete that inaccurate calculation and instead use the Autofill to complete the rest of the columns by using the first row’s calculation
Right here I am trying to convert the difference of time worked into a number but look what happens: Excel is looking at these as a small portion of a day, which has 24 hours.
Since Excel is seeing the time as buckets of 24 hours, we will have to help Excel get the right calculation by multiplying this difference that we see here times 24
You are going to see that since Excel is multiplying the number by a time the result is a time format or 0, you are going to fix it by turning this result into a number as follows:
Select the formatted cells you need to change, go to the Home Tab >> Number group >> Select the Number format or click the comma right below.
Let’s go ahead and autofill by just double clicking on the lower right-hand corners. Let's do it with the Total hours column, the rate and total rate because we are testing our formula using another row.
So let me enter time in 9 am and time out 5 pm, don't forget those spaces, look what happens: because we multiplied by 24 and format the total hours as a number, I can reuse this repeatedly. No more manual counting and we hope you enjoyed this tutorial, make sure to check out the rest of our blog filled with technology tips.
Comentários