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

Report •


#1
April 20, 2010 at 10:13:46
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 a number with 6 decimal places

Now 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


Ask Question