Transfer time hh:mm to decimal and round

D

DBsWifeLB

Anyone know how to transfer a time (hh:mm) into a rounded decimal?
example: 3:08 to be 3.25??
 
L

Llobid

I'm not sure if this is what you're asking, but if I have my time i
cell A1, my formula might look like this...

=(HOUR(A1)*60+MINUTE(A1))/60

Format the cell as "number" showing as many decimals as you need. Th
time conversions would look like this:

3:15 would yield 3.25

4:30 would yield 4.50 (with decimals set at 2)

13:12 would yield 13.20...etc.

I'm assuming you want the decimals to show the minutes as fractions o
an hour...if not, this isn't what you want
 
D

DBsWifeLB

This does work to get it to a decimal, but I need it also to round t
the nearest quarter hour.
3.0
3.25
3.5
3.75
Any other ideas
 
L

Llobid

I'm assuming that you want to do something like take a time card an
convert the hours worked to the nearest quarter hour. If I'm correct
this ridiculously long formula seems to work OK. If your data (times
are in Column A, your formula would look like this:

=IF(MINUTE(A1)>=53,HOUR(A1)+1&"."&"00",HOUR(A1)&"."&IF(MINUTE(A1)<=7,0,IF(MINUTE(A1)<=22,25,IF(MINUTE(A1)<=37,50,IF(MINUTE(A1)<=52,75,0)))))

This gets the following results on my spreadsheet (formatting the cell
as "number" and showing 2 decimals:

3:30 yields 3.50

15:08 yields 15.25

7:53 yields 8.00

4:21 yields 4.25

18:39 yields 18.75

You may have to adjust the values to round the way you want, but
think it should work
 
R

RagDyer

Just following your lead of converting to decimals, this should round to
what's being asked for:

=ROUND(((HOUR(A1)*60+MINUTE(A1))/60)*4,0)/4
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I'm assuming that you want to do something like take a time card and
convert the hours worked to the nearest quarter hour. If I'm correct,
this ridiculously long formula seems to work OK. If your data (times)
are in Column A, your formula would look like this:

=IF(MINUTE(A1)>=53,HOUR(A1)+1&"."&"00",HOUR(A1)&"."&IF(MINUTE(A1)<=7,0,IF(MI
NUTE(A1)<=22,25,IF(MINUTE(A1)<=37,50,IF(MINUTE(A1)<=52,75,0)))))

This gets the following results on my spreadsheet (formatting the cells
as "number" and showing 2 decimals:

3:30 yields 3.50

15:08 yields 15.25

7:53 yields 8.00

4:21 yields 4.25

18:39 yields 18.75

You may have to adjust the values to round the way you want, but I
think it should work.
 
Top