How to Transform Time into Numbers in Excel

Hello in this tutorial let us show you how to transform time into numbers 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 and every 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 for 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.

If you like this tutorial don’t forget to like and subscribed to continue sharing more tips with you

Now my hours look good and this is the part where we start getting deeper on 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 substract 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 is incorrect.

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 getting 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 end result is a time format or 0, you are going to fix it by turning this result into a number by selecting the number format or by selecting the comma right here, either way will work

I am going to autofill by just double clicking on the lower right hand corners. Let’s do it with 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 over and over again. No more manual counting and we hope you enjoyed this tutorial, see you on the next video!

how to convert time into hours in excel
convert number to hours and minutes in excel
convert to time excel
excel convert time to seconds
excel convert number to time hhmm
convert time to text in excel
convert time to decimal
excel convert number to minutes
how to convert time into hours in excel
excel convert time to seconds
convert to time excel
excel convert number to time hh:mm
convert time to decimal
excel convert number to minutes
how to subtract time in excel and convert to number
how to convert date and time to number in excel
how to convert decimal number to time in excel
how to convert date and time format to number in excel
how to convert 4 digit number to time in excel
how to convert 6 digit number to time in excel
how to convert date and time to serial number in excel
how to convert text time to number in excel
how to convert general number to time in excel