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

azkhan

New Member
Joined
Jun 9, 2010
Messages
29
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 :)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

azkhan

New Member
Joined
Jun 9, 2010
Messages
29
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jun 9, 2010
Messages
29

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jun 9, 2010
Messages
29

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jun 9, 2010
Messages
29
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.
 

Forum statistics

Threads
1,141,405
Messages
5,706,261
Members
421,437
Latest member
GijoeBlack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top