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