Converting a Number to a Time Value





To quickly type time values into cells:

Enter the following formula:

=IF(A31000,TIMEVALUE(LEFT(A3,1)&”:”&RIGHT(A3,2)),TIMEVALUE(LEFT(A3,2)&”:”&RIGHT(A3,2)))

The formatting of the cells in column B is HH:MM.
Screenshot // Converting a Number to a Time Value
Converting a Number to a Time Value



18 thoughts on “Converting a Number to a Time Value

  1. there seems to be a problem with this formula when it comes to entering 12:00 midnight (24:00) or beyond it. i run an internet cafe and this formula has helped a lot but the problem is, im sometimes open til the wee hours of the morning because of clients that would log in at 11pm and leave at 2 am. so how do i solve entering their time?

  2. “Use the following function to convert a 6-digit number to time with second in it:
    e.g. 233045 – 23:30:45
    LEFT(H1,2)&””:””&MID(H1,3,2)&””:””&RIGHT(H1,2)”

  3. How about the inverse problem, I have an elapsed time in format of HH:MM (hours and minutes), I would like to covert to a general number for costing purposes… in other words 2:06 (2 hours six minutes) = 2.1 hours

  4. “If you have the data in cell A:1, type the following information into A:2
    =HOUR(A1)+((MINUTE(A11)*0.01)*(1.66))
    I used this formula while creating an employee time sheet. I wanted to also calculate the overtime hours, so I used the following formula in A:3 (You can format the cell or use conditional formatting to highlight the cell when overtime hours exceed a certain # of hrs)
    When attempting to revert decimals back to time format, I had a bit of a problem. Excel doesn’t easily support that type of activity, but I was able to get around it and would be happy to share that information with anyone who find it useful.
    =IF((L11>8),L11-8,0) “

  5. “Hi Melissa,
    Getting from a decimal to a ‘time’ format is fairly simple:
    If you have a decimal entry in cell B2 for hours (say, 2.1 hours = 2 hours 6 mins), and you want to convert from 2.1 to “”2:06″” then use the following:
    =TEXT(B2/24,””HH:mm””) {=””02:06″”}
    You can change the actual time format as for any other number format.
    Alan. “

  6. “Hi Scott,
    If you have a cell B9 with hours and minutes (and seconds if you want) containing: 02:06 and you want to convert to decimal (2.1 hours) then the following formula will do that:
    =B9*24
    It is that simple, but be careful to ensure that the cells are correctly formatted to display in the way you want (the formulae cell above could use the “”General”” format to show 2.1).
    Alan.”

  7. “Thanks Guys! Didn’t realize it was that simple, I feel dumb now = )
    If you have any other interesting stuff you wish to pawn off, let me know. I’m trying to get into VBA and am pretty good with excel and access, but it seems as though I have a lot more to learn!
    Thanks a Bunch! “

  8. there seems to be a problem with this formula when it comes to entering 12:00 midnight (24:00) or beyond it. i run an internet cafe and this formula has helped a lot but the problem is, im sometimes open til the wee hours of the morning because of clients that would log in at 11pm and leave at 2 am. so how do i solve entering their time?

  9. “Use the following function to convert a 6-digit number to time with second in it:
    e.g. 233045 – 23:30:45
    LEFT(H1,2)&””:””&MID(H1,3,2)&””:””&RIGHT(H1,2)”

  10. How about the inverse problem, I have an elapsed time in format of HH:MM (hours and minutes), I would like to covert to a general number for costing purposes… in other words 2:06 (2 hours six minutes) = 2.1 hours

  11. “If you have the data in cell A:1, type the following information into A:2

    =HOUR(A1)+((MINUTE(A11)*0.01)*(1.66))

    I used this formula while creating an employee time sheet. I wanted to also calculate the overtime hours, so I used the following formula in A:3 (You can format the cell or use conditional formatting to highlight the cell when overtime hours exceed a certain # of hrs)

    When attempting to revert decimals back to time format, I had a bit of a problem. Excel doesn’t easily support that type of activity, but I was able to get around it and would be happy to share that information with anyone who find it useful.

    =IF((L11>8),L11-8,0) “

  12. “Hi Melissa,

    Getting from a decimal to a ‘time’ format is fairly simple:

    If you have a decimal entry in cell B2 for hours (say, 2.1 hours = 2 hours 6 mins), and you want to convert from 2.1 to “”2:06″” then use the following:

    =TEXT(B2/24,””HH:mm””) {=””02:06″”}

    You can change the actual time format as for any other number format.

    Alan”

  13. “Hi Scott,

    If you have a cell B9 with hours and minutes (and seconds if you want) containing: 02:06 and you want to convert to decimal (2.1 hours) then the following formula will do that:

    =B9*24

    It is that simple, but be careful to ensure that the cells are correctly formatted to display in the way you want (the formulae cell above could use the “”General”” format to show 2.1).

    Alan.”

  14. “Thanks Guys! Didn’t realize it was that simple, I feel dumb now = )

    If you have any other interesting stuff you wish to pawn off, let me know. I’m trying to get into VBA and am pretty good with excel and access, but it seems as though I have a lot more to learn!

    Thanks a Bunch!”

Leave a Reply

Your email address will not be published. Required fields are marked *


7 + = thirteen

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>