MrExcel Publishing
Your One Stop for Excel Tips & Solutions

time calculation


Posted by SANTOSH MENON on November 23, 2001 10:35 PM

I WOULD LIKE TO KNOW HOW TO CALCULATE TIME IN EXCEL USING THE FUNTION TOOL. FOR EXAMPLE I WOULD LIKE TO CALCULTE THE ARRIVAL, DEPARTURE, OVERTIME OF AN EMPLOYEE AS SHOWN BELOW:

TIMINGS 9:45 A.M TO 18:30 P.M

ARR.TIME LT.HRS DEP.TIME ELY.HRS T.HRS OVERTIME

10:00 .15MIN 19:45 0 9:45 .45

THANK YOU


Posted by Shamsuddeen. P.K on November 24, 2001 9:53 AM

Posted by Shamsuddeen. P.K on November 24, 2001 10:01 AM

Please find below the formula to calculate normal hours and overtime hours

Cell A2 = Arrival Time
Cell B2 = Departure time
Type the following formula in cell C2 to find the Normal working hours

=IF(B2-A2>TIME(8,45,0),TIME(18,30,0)-A2,B2-A2)

And in Cell D2 type the following formula to find the Overtime Hours

=B2-A2-C2


Hope I have answered your question rightly.

Shamsudeen. P.K