On Mon, 24 Dec 2007 22:34:01 -0800, yclhk
wrote:

I'd like to create a excel file to calculate the hours worked.
Col A Col B Col C Col D
Date Time IN Time OUT hrs worked

in col B, record Time IN 7:53am
in col C, record Time OUT 6:05pm
However, 7:53am expects to count as 8:00am &
6:05pm count as 6:00pm, and hours worked results as 10 hours

I wish to keep the records of the Time IN & OUT in the actual time and
how to set the formula to calculate in form of the expected result in col D ?

Assuming you want to round to the nearest 1/4 hour, use one of the following
formulas, depending on whether or not you have the Analysis Tool Pak installed.

If you want to round to the nearest hour, or 30 minutes, change the "15" in the
formula to 30 or 60.

=ROUND(Time_OUT/TIME(0,15,0),0)*TIME(0,15,0)-ROUND(Time_IN/TIME(0,15,0),0)*TIME(0,15,0)

=MROUND(Time_OUT,TIME(,15,))-MROUND(Time_IN,TIME(,15,))

The formulas assume that there will be no work hours that span midnight, but
they can be altered to take that into account if necessary.

FORMAT the result as [h]:mm (or similar)
--ron