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