formula to calculate work hours between two dates of a work week - Sunday to Thursday.

[azkhan]

Hi there,

Please help me with a formula for the situation below.

Work week is from Sunday to Thursday (8.30 am to 5.30 pm with one hour lunch break)

Example:
StartDate: 01/06/2010 10:00 AM in A1
EndDate: 09/06/2010 04:00 PM in B1

I need to calculate work hours (in decimal format e.g 30.75 hours) between the above start and end date_time considering a work day is of 8 hours and non-working days are Fridays & Saturdays along with any HolidayList.

I'm Feeling Lucky

 

[Andrew Poulsom]

What about holidays? Here is something you can use for whole days:

http://www.cpearson.com/excel/betternetworkdays.aspx

Calculate the whole days from StartDate+1 to EndDate-1 and multiply by 8. Then add the hours between StartTime and 17:30 and 08:30 and EndTime, allowing for lunch (you haven't given lunch times).

 

[azkhan]

Thanks Andrew - The lunch hour is not fixed however each employee's work day is calculated as 8 hours.
I tried the formula from this link but it did not work. I believe it is for calculating only work days but not hours.

Q re your suggestion above... Lets say, if the lunch hour is not fixed, will this formula help me?

 

[Andrew Poulsom]

Did you remember to multiply by 8 (hours)?

I think you would need to know the lunch times. You would want to deduct an hour only if the employee started before lunch (provided that he finished after lunch).

By the way, how could one be certain that the employee worked full days between StartDate and EndDate?

 

[azkhan]

I did try that but it multiplies the number of days by 8 which does not provide me the appropriate result. I need this formula to calculate TAT for various activities of a process. Lets ignore the exclusion of lunch break requirement and loaok at this situation:
E.g. An activity was started at 02/06/2010 12:00 pm and completed at 03/06/2010 5:00 pm. The actual result should be 14 hours. However with the formula you suggested, it gives me 16 hours.
I tried the below formula that worked perfectly right for hours but takes Sat & Sun as weekends.

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),(24*(DayEnd-DayStart)*(XYZ)+INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),ROUND((24*(DayEnd-DayStart)),2))))))

I tried using cpearson formula (without holidays) with the above one but did not work.
Just wondering if you could please help me combining both or with a new one.

Many thanks in advance.

Cheers,
Az

 

[Andrew Poulsom]

For your sample dates (which are only one day apart) this formula:

=(("17:30"-MOD(A1,1))+(MOD(A2,1)-"08:30"))*24

returns 14.

 

[azkhan]

Will this also work for start and end days with a gap of more than one day?

Just thinking if we can tweak the formula from cpearson to work for my situation.

sorry, the formula i mentioned above is missing some text from the middle. Here's the right one

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),(24*(DayEnd-DayStart)*(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),ROUND((24*(DayEnd-DayStart)),2))))))

 

[Andrew Poulsom]

Please give some sample start and end dates (and times) and the expected result. You need this formula from Chip:

=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),1,0))

Add 1 to start date and subtract 1 from end date and multiply by 8. Then tack my formula in post #6 on the end.

 

[azkhan]

Sample: dd/mm/yyyy
StartDate: 03/06/2010 10:00 AM
EndDate: 09/06/2010 17:00 PM
StartTime: 8:30 AM
EndTime: 5:30 PM

Expected result - 43.5 hours.

In this sample Friday and Saturday are excluded as weekends however Sundays are counted.

 

[azkhan]

Correction << Expected result - 43 hours