Tom Manter
New Member
When working with time calculations, how do I convert an Excel cell expressed as hours:minutes:seconds to hundredths of an hour, and also do the reverse calculation from hundredths to hours/minutes?
Time calculations (payroll hours)
- Thread starter Tom Manter
- Start date
bobhc
Excel Ninja
Good day Tom and welcome to the forum please fread this link as it will help you in using the forum
http://chandoo.org/forum/forums/new-users-please-start-here.14/
As to your question try this (assuming your data is in A1)
=A1/24,
and format the result in a time format to suit your preferences.
.
http://chandoo.org/forum/forums/new-users-please-start-here.14/
As to your question try this (assuming your data is in A1)
=A1/24,
and format the result in a time format to suit your preferences.
.
SirJB7
Excel Rōnin
Hi, Tom Manter!
If in cell A1 is your time value in hh:mm:ss, for converting it to hundredths of hour try in B1:
=HORA(A1)+MINUTO(A1)/60+SEGUNDO(A1)/3600 -----> in english: =HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600
The reverse process is a bit tricky, try this in C1:
=HORANUMERO(ENTERO(B1)&":"&ENTERO((B1-ENTERO(B1))*60)&":"&ENTERO(((B1-ENTERO(B1))*60-ENTERO((B1-ENTERO(B1))*60))*60)) -----> in english: =TIMEVALUE(INT(B1)&":"&INT((B1-INT(B1))*60)&":"&INT(((B1-INT(B1))*60-INT((B1-INT(B1))*60))*60))
A1 & C1 formatted as hh:mm:ss and B1 as General.
Regards!
If in cell A1 is your time value in hh:mm:ss, for converting it to hundredths of hour try in B1:
=HORA(A1)+MINUTO(A1)/60+SEGUNDO(A1)/3600 -----> in english: =HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600
The reverse process is a bit tricky, try this in C1:
=HORANUMERO(ENTERO(B1)&":"&ENTERO((B1-ENTERO(B1))*60)&":"&ENTERO(((B1-ENTERO(B1))*60-ENTERO((B1-ENTERO(B1))*60))*60)) -----> in english: =TIMEVALUE(INT(B1)&":"&INT((B1-INT(B1))*60)&":"&INT(((B1-INT(B1))*60-INT((B1-INT(B1))*60))*60))
A1 & C1 formatted as hh:mm:ss and B1 as General.
Regards!
NARAYANK991
Excel Ninja
Hi Tom ,
A time is just a number , only formatted differently.
A time which is displayed as 5:35:23 is the same value as the decimal number 0.232905092592593
To get the displayed value in hundredths of an hour , just multiply it by 24 ; this is because the 24 hours from one midnight to the next go , in terms of decimal numbers , from 0 to 1.
When you multiply 0.232905092592593 by 24 , the result is : 5.58972222222223
All you have to do is format the cell which contains the multiplied value as Number , with the required number of decimal places.
Thus if your time value is in A1 , formatted to display 5:35:23 , in B1 put in the formula =A1*24 , and format B1 as Number.
To go the other way , do the inverse of multiplication i.e. division. If you have the value 5.58972222222223 in B1 , in C1 put in the formula =B1/24 , and format C1 as [h]:mm:ss
Narayan
A time is just a number , only formatted differently.
A time which is displayed as 5:35:23 is the same value as the decimal number 0.232905092592593
To get the displayed value in hundredths of an hour , just multiply it by 24 ; this is because the 24 hours from one midnight to the next go , in terms of decimal numbers , from 0 to 1.
When you multiply 0.232905092592593 by 24 , the result is : 5.58972222222223
All you have to do is format the cell which contains the multiplied value as Number , with the required number of decimal places.
Thus if your time value is in A1 , formatted to display 5:35:23 , in B1 put in the formula =A1*24 , and format B1 as Number.
To go the other way , do the inverse of multiplication i.e. division. If you have the value 5.58972222222223 in B1 , in C1 put in the formula =B1/24 , and format C1 as [h]:mm:ss
Narayan