I have a time sheet that has worked good enough so far but now I need to be able to show total hours for the day in a decimal and also round my time to every 15 minuts. Is there formula that can take a time and convert it to decimals and round?

Hi, If you have Start times in column A

and End times in column B

(Both columns A and B are formatted as time hh:mm)Assuming the first pair of times is in row 1, then

Enter this in cell C1:=(B1-A1)/(1/24)

Format cell C1 as anumberwith 6 decimal placesNow enter this formula in cell D1:

=IF(MOD((B1-A1)/(1/24),1)<0.125,INT((B1-A1)/(1/24)), IF(MOD((B1-A1)/(1/24),1)<0.375,INT((B1-A1)/(1/24))+0.25, IF(MOD((B1-A1)/(1/24),1)<0.625,INT((B1-A1)/(1/24))+0.5, IF(MOD((B1-A1)/(1/24),1)<0.875,INT((B1-A1)/(1/24))+0.75, INT((B1-A1)/(1/24))+1))))

This formula has been split onto more than one line, so you will need to reconstitute it back to a single string.Format D1 as a

numberwith two decimal places.Here are some examples of the results:

A B C D 11:00 12:00 1.000000 1.00 11:00 12:07 1.116667 1.00 11:00 12:08 1.133333 1.25 11:00 12:22 1.366667 1.25 11:00 12:23 1.383333 1.50 11:00 12:30 1.500000 1.50 11:00 12:37 1.616667 1.50 11:00 12:38 1.633333 1.75 11:00 12:52 1.866667 1.75 11:00 12:53 1.883333 2.00 11:00 12:59 1.983333 2.00 11:00 13:00 2.000000 2.00 11:00 13:01 2.016667 2.00

Hope this is of use.Regards

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History