# excel time sheet formulas April 20, 2010 at 05:08:09
Specs: Windows XP
 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? See More: excel time sheet formulas

#1 April 20, 2010 at 10:13:46
 Hi,If you have Start times in column Aand 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, thenEnter this in cell C1: `=(B1-A1)/(1/24)`Format cell C1 as a number with 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 number with 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

Report •
Related Solutions 