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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
I have a value 2.7, and want to convert into time format as 3:10. Pl can anyone help me in this.
Hi Rohi,
Assuming you have value in A1 then in B1, enter the following formula. It will return as time value.
=TEXT(TIME(INT(A1),MOD(A1,1)*100,),"HH:MM")+0
The only thing you have to do is that you have to format the B1 in Time format.
Thanks,
Site Admin
"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!"
"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."
"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"
"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) "
I found that it was almost impossible to find an easy solution to this. I had to create approx 10 hidden fields inorder to convert decimals into time. If anyone has any questions on how to handle it, my email is mparkda99@hotmail.com
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
"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)"
This formula seems fine, what about if there are seconds in the time, i.e. 233045 - 23:30:45?
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?
"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! "
"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."
"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. "
"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) "
I found that it was almost impossible to find an easy solution to this. I had to create approx 10 hidden fields inorder to convert decimals into time. If anyone has any questions on how to handle it, my email is mparkda99@hotmail.com
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
"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)"
This formula seems fine, what about if there are seconds in the time, i.e. 233045 - 23:30:45?
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?