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

#### azkhan

##### New Member
Hi there,

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).

#### Andrew Poulsom

##### MrExcel MVP
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

##### New Member
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
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

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.

Cheers,
Az

#### Andrew Poulsom

##### MrExcel MVP
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

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
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
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

##### New Member
Correction << Expected result - 43 hours

Replies
4
Views
670
Replies
2
Views
249
Replies
6
Views
435
Replies
0
Views
734
Replies
8
Views
609

### Forum statistics

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.

### Which adblocker are you using?    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

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