Calculating working hours between 2 dates with a SharePoint twist!

[enigma121]

Hi,

I have read post upon post related to this topic but unfortunately none of them produce the effect I am hoping for. Hopefully someone with more excel knowledge will be able to provide some helpful hints!

What I am looking for:

I need to calculate working hours (Monday-Friday) between 2 dates. To add an extra layer to this the working hours differ depending on the day of the week)

Monday -Thursday: 07:45 - 16:45 (9 hours)
Friday: 07:45 - 11:45 (4 hours)

I have found a formula (salvaged from Google) that does this perfectly (where A2 is the start date and B2 is the end date):

=((SUMPRODUCT(INT((WEEKDAY(A2-{2,3,4,5,6})+INT(B2)-INT(A2))/7),{9,9,9,9,4})+(MOD(B2,1)-MOD(A2,1))*24+IF(WEEKDAY(A2)=7,8,7)-IF(WEEKDAY(B2)=7,12,16))/24)*24

Unfortunately, here is where we come to the 'twist' part of the question! This calculation is required for a SharePoint 2010 calculated column and SharePoint calculated columns do not support array formulas such as SUMPRODUCT()!

I have tried creating 5 formulas to calculate the working hours for Monday, Tuesday, Wednesday, Thursday and Friday using something like:

Working hours for Wednesday:

=(INT((B2-WEEKDAY(B2+1-4)-A2+8)/7)*("16:45"-"07:45")+MOD(B2,1)-MOD(A2,1))*24

But this does not work - The formula above shows double the amount of hours that there should be in the day (if dates 10/12/2012 07:45:00 - 12/12/2012 16:45:00 are used). IE. where there should be 9 hours it shows 18, where there should be 0 it shows 9!

I guess I am looking to see if it is indeed possible to create a formula that calculates working hours between 2 dates (either 5 separate formulas for each working day or one single one for the whole week) using no array formulas.

NOTE: I am using the WEEKDAY() formula simply because SharePoint doesn't support NETWORKDAYS()

One last point (that might help someone else reading this). If you work the same hours every day of the week this formula works:

=(((FLOOR(B2-A2,0.5))-INT((FLOOR(B2-A2,0.5)/7))*2-IF((WEEKDAY(B2)-WEEKDAY(A2))<0,2,0))*("16:45"-"07:45")+MOD(B2,1)-MOD(A2,1))*24

Unfortunately our company chose to be difficult so this doesn't work for us * cries * lol

Any help would be much appreciated! If you don't understand anything I'd be happy to clarify.

Thanks a lot

Dave

 

[barry houdini]

Hello Dave, welcome to MrExcel

Looking at the formulas you are using I assume that the start and end date/times will always fall within the working hours (e.g. you won't have a start time on a Saturday or a finish time after 16:45 on a Thursday).

Your last formula for the same hours MF doesn't work for me in all cases, I think this would be more accurate:

=((INT(B2)-INT(A2)-INT((WEEKDAY(A2)+INT(B2)-INT(A2))/7)-INT((WEEKDAY(A2-1)+INT(B2)-INT(A2))/7))*("16:45"-"7:45")+MOD(B2,1)-MOD(A2,1))*24

This formula will calculate Monday hours only:

=24*((INT((WEEKDAY($A2-2)+INT($B2)-INT($A2))/7))*("16:45"-"7:45")-(WEEKDAY($B2)=2)*("16:45"-MOD($B2,1))-(WEEKDAY($A2)=2)*(MOD($A2,1)-"7:45"))

The red 2 indicates Monday - change to 3, 4, 5 or 6 for Tuesday to Friday and obviously you might need to change the hours in green for each day

 

[enigma121]

Edit: Apologies (again) if this double posts! - I tried once but I.E. decided that it would crash at the same time as posting reply! ..Anyway...

You are a legend! Thanks so much for this, it works like a charm!

After looking at it and breaking it down in Excel though, I feel a little like a stone age man if he came to our time ...slightly bewildered!

Would it be possible for you to explain what is going on - specifically the second part after the number of weekdays to multiply has been calculated.

Thanks a lot for all your help

Dave

 

[barry houdini]

Would it be possible for you to explain what is going on - specifically the second part after the number of weekdays to multiply has been calculated.

I assume you are referring to the Monday hours formula

In short the idea is to calculate the number of Mondays included in the period - including start and end date, so the blue part does that

=24*((INT((WEEKDAY($A2-2)+INT($B2)-INT($A2))/7))* ("16:45"-"7:45") -(WEEKDAY($B2)=2)*("16:45"-MOD($B2,1))-(WEEKDAY($A2)=2)*(MOD($A2,1)-"7:45"))

.....and the green part gives you the number of hours for each day, so that's the total hours (those two multiplied together)......except that will include the whole of the day if the period starts or ends on a Monday, so in that case we need to check if the end date is a Monday, and if so deduct any hours between the time on that day and the start or finish time, so this part does that

(WEEKDAY($B2)=2)*("16:45"-MOD($B2,1))

That will resolve to zero if B2 is any day other than a Monday, but if it is a Monday then MOD($B2,1) extracts the time from B2 and you get the difference between that time and 16:45

Similarly with this part:

(WEEKDAY($A2)=2)*(MOD($A2,1)-"7:45")

If A2 is a Monday only then that will give you the time period between 07:45 and the start time on that date....and subtract that from the total

If you're interested I came up with a formula that will give you Monday to Thursday hours combined (assuming same times on those four days) so you could use this formula added to a single formula for Friday hours to give you the total

=24*(((INT(B2)+8-WEEKDAY(B2,2)-INT(A2)+WEEKDAY(A2,3))/7*4-MIN(WEEKDAY(A2,3),4)-MAX(0,3-WEEKDAY(B2,3)))*("16:45"-"7:45")-IF(WEEKDAY(A2,2)<=4,MOD(A2,1)-"7:45")-IF(WEEKDAY(B2,2)<=4,"16:45"-MOD(B2,1)))

 

[enigma121]

Wow! Thanks for that. I totally understand now.

In fact, it has helped me to subtract the lunch break from the final value (assuming the individual doesn't enter their start or finish time during the lunch break)

(B2 = start time, C2 = end time, formula below for Monday)

=24*((INT((WEEKDAY(B2-2)+INT(C2)-INT(B2))/7))*(("16:45"-"07:45"))-(WEEKDAY(C2)=2)*("16:45"-MOD(C2,1))-(WEEKDAY(B2)=2)*(MOD(B2,1)-"7:45")-((MOD(B2,1)<"12:31"+0)-(MOD(C2,1)<"12:31"+0))*("13:00"-"12:30"))

Once again, thanks so much for all the help on this!

Also, thanks for the additional formula that calculates Monday-Thursday hours

Much appreciated

Dave

 

[enigma121]

Edit time ..timed out!

It does look like I was slightly too eager to post about my achievement though! It only works when the Monday is not encapsulated within the date range or indeed when the start and end dates don't start and end on different Mondays... Time to rethink!

 

[enigma121]

OK so after pondering for a while I think I've finally cracked it!

The following assumes Start date/time in B2 and End Date/time in C2 along with lunch breaks of 13:30-13:00 Monday-Thursday (no lunch break on Friday due to the times worked)

Mondays Formula:

=24*((INT((WEEKDAY(B2-2)+INT(C2)-INT(B2))/7))*(("16:45"-"07:45")-("13:00"-"12:30"))-(WEEKDAY(C2)=2)*("16:45"-MOD(C2,1))-(WEEKDAY(B2)=2)*(MOD(B2,1)-"7:45"))+IF(AND(WEEKDAY(B2)=2,MOD(B2,1)>"13:00"+0),0.5,0)+IF(AND(WEEKDAY(C2)=2,MOD(C2,1)<"12:30"+0),0.5,0)

Red Bits indicate the parts that deal with lunch break - basically subtract the 30 minutes lunch from each day and then add on 30 minutes if the start time is after lunch or the end time is before lunch (thus no lunch break should be accounted for)

Green parts would need to be changed to identify Tuesday-Thursday (3-5) using the same formula

Fridays formula:

=24*((INT((WEEKDAY(B2-6)+INT(C2)-INT(B2))/7))*(("11:15"-"07:45"))-(WEEKDAY(C2)=6)*("11:15"-MOD(C2,1))-(WEEKDAY(B2)=6)*(MOD(B2,1)-"7:45"))

Due to the hours worked (7.45-11.15) our factory workers don't have a lunch break on a Friday so the red parts from above have unnecessary.

All these formulas assume that start and end times are within working hours

Once again thanks for all the help Barry! You are a legend!