We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
Depending on what you’re calculating, you may need to know how to convert a time format to a number in Excel.
This will come in handy if you need to calculate total time, for example.
And doing so is all about knowing the right math!
What to Know About Time Values in Excel
Excel sees all values in terms of decimal numbers. This includes those values that have been formatted to visually look like a time value.
So, to convert a time format to a number in Excel, it’s important to understand that time in Excel is just a type of format.
The values displayed as time are based off a 24-hour day. Where each hour is 1/24 of a day or 4.17% of a day.
NOTE: Time in Excel is a value based on a 24-hour day. Each hour is 1/24 or 4.17% of a day.
For example, 8 am as a decimal would be 0.33 or 33% of a day. And 33% of a day is 8 hours (0.33 x 24).
How to Convert a Time Format to a Number in Excel
WATCH: Convert Time to a Number in Excel (video tutorial)
Now that we know that Excel sees time as a portion of a 24-hour day, we can use this information to convert a time format that displays hours, minutes, and seconds to a decimal value.
We can then calculate how much time in a day that value represents, again, as a decimal value.
Here’s how –
Convert Time in Hours to a Number in Excel
To convert hours to a number, you would multiply the time value by 24.
Let’s say that we need to know how many hours 8:23:45 am (8:23 am and 45 seconds) is as a number. To figure this out, you would multiply the time value by 24.
Your result for total number of hours this represents should be 8.40.
TIP: Make sure the cell in which you are doing this calculation is NOT formatted as time!
Likewise, 16:47:13 (I’m using military time for clarity) would be a total of 16.79 hours.
Let’s say someone clocked in at 8:23:45 am and clocked out at 4:47:13 pm, by converting these times to numbers, you can calculate that they worked 8.39 hours.
Convert Time in Minutes to a Number in Excel
To calculate the total number of minutes a time format is, you would multiply by 24 and by 60.
Because 24 hours in a day and 60 minutes in an hour.
To figure out how many minutes in a day 8:23:45 am is, you would multiply this time format by 24, then by 60.
This will result in 503.75 minutes.
TIP: You can also multiply by 1,440, but I find it easier for my brain to multiply by 24, then by 60.
To stay with the example of clocking in at 8:23:45 am (503.75 minutes) and clocking out at 4:47:13 pm (1,007.22 minutes), this would be a total of 503.47 minutes worked.
Convert Time in Seconds to a Number in Excel
If you’ve been reading from the beginning, you may have noticed a pattern. If so, you guessed it! – to calculate how many seconds a time value represents, you would multiply by 24, then by 60, then by 60 again.
Because 24 hours in a day, 60 minutes in an hour, and 60 seconds in a minute.
For example, to calculate how many seconds of a day 8:23:45 am represents, we would multiply the time value by 24, then by 60, then by 60 again. The result would be 30,225 seconds.
TIP: You can also multiply by 86,400 if you prefer.
This means that, if someone clocked in at 8:23:45 am and clocked out at 4:47:13 pm, they worked a total of 30,208 seconds.
NEXT: How to Choose a Time Format in Excel (video tutorial)
FYI: this tutorial was created using the desktop version of Excel in Microsoft 365.
Leave a Reply