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

 

[azkhan]

I did confirm it with Ctrl+Shift+Enter.

 

[azkhan]

This is how it looks in the formula bar:
{=(SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(INT(A1)+1&":"&INT(B1)-1))),{6,7},0)),1,0))*9)+(((A5-MOD(A1,1))+(MOD(B1,1)-A4))*24)}

 

[barry houdini]

A1 - 03/06/2010 10:00:00
B1 - 09/06/2010 17:00:00

A4 - 08:30
A5 - 17:30

The result i am expecting is 43 hours (43 hours from 5 working days 3,6,7,8 & 9)

Given those values both Andrew's formula

=(SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(INT(A1)+1&":"&INT(B1)-1))),{6,7},0)),1,0))*9)+(((A5-MOD(A1,1))+(MOD(B1,1)-A4))*24)

and my version

=((NETWORKDAYS(A1+1,B1+1)-1)*(A5-A4)+MOD(B1,1)-MOD(A1,1))*24

give me a result of 43. You can adapt either to exclude holidays as well. Neither formula will cater for start or end times that are not with the "business hours". If you want to cater for that you can use this adaptation of my suggestion:

=((NETWORKDAYS(A1+1,B1+1)-1)*(A5-A4)+IF(NETWORKDAYS(B1+1,B1+1),MEDIAN(MOD(B1,1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1)*MOD(A1,1),A5,A4))*24

....and add holiday range to each NETWORKDAYS function (as shown in my earlier post, i.e. INDEX(holidays+1,0) if you want to exclude holidays as well

 

[azkhan]

it works perfectly well for my situation. Thank you so much barry. You are the best!!!

Barry, I would like to ask one more favor on the same....

I have copied this formula to numerous cells and as the start and end date/time is not entered yet, it shows '0' in the cells. I was just wondering if you can help with a formula:
1. that can show " " if no values are entered
2. if only start dt/time is entered and no end dt/time then it should take NOW() instead of B1 (EndDate) in the formula you suggested.

What I did:
1. IF(A1<0," ",IF(B1>0,(((NETWORKDAYS(A1+1,B1+1,$Z$3:$Z$100)-1)*(A5-A4)+IF(NETWORKDAYS(B1+1,B1+1),MEDIAN(MOD(B1,1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1)*MOD(A1,1),A5,A4))*24),(((NETWORKDAYS(A1+1,NOW()+1,$Z$3:$Z$100)-1)*(A5-A4)+IF(NETWORKDAYS(NOW()+1,NOW()+1),MEDIAN(MOD(NOW(),1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1)*MOD(A1,1),A5,A4))*24)))

 

[azkhan]

What I did:
1. IF(A1<0," ",IF(B1>0,(((NETWORKDAYS(A1+1,B1+1,$Z$3:$Z$100)-1)*(A5-A4)+IF(NETWORKDAYS(B1+1,B1+1),MEDIAN(MOD(B1,1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1)*MOD(A1,1),A5,A4))*24),(((NETWORKDAYS(A1+1,NOW()+1,$Z$3:$Z$100)-1)*(A5-A4)+IF(NETWORKDAYS(NOW()+1,NOW()+1),MEDIAN(MOD(NOW(),1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1)*MOD(A1,1),A5,A4))*24))

 

[azkhan]

With my try I am able to calculate with NOW() if EndDate is null however if both Start & End dates are null it gives me this result '259342.9503'.

 

[azkhan]

Got it! Just tried this and it works!!!

=IF(A1>0,IF(B1>0,(((NETWORKDAYS(A1+1,B1+1,$Z$3:$Z$100)-1)*(A5-A4)+IF(NETWORKDAYS(B1+1,B1+1),MEDIAN(MOD(B1,1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1)*MOD(A1,1),A5,A4))*24),(((NETWORKDAYS(A1+1,NOW()+1,$Z$3:$Z$100)-1)*(A5-A4)+IF(NETWORKDAYS(NOW()+1,NOW()+1),MEDIAN(MOD(NOW(),1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1)*MOD(A1,1),A5,A4))*24))," ")

It is not neat though.

 

[azkhan]

Barry/Andrew, thanks a ton for your help. Highly appeciate it.

 

[barry houdini]

You could use this version

=IF(A1="","",((NETWORKDAYS(A1+1,IF(B1="",NOW(),B1)+1,$Z$3:$Z$100)-1)*(A5-A4)+IF(NETWORKDAYS(IF(B1="",NOW(),B1)+1,IF(B1="",NOW(),B1)+1,$Z$3:$Z$100),MEDIAN(MOD(IF(B1="",NOW(),B1),1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1,$Z$3:$Z$100)*MOD(A1,1),A5,A4))*24)

 

[lje-s]

I am using the formula from above, but I want to include a set lunch hour from 12:00 to 13:00. How can I adust the formula below accordingly?

=((NETWORKDAYS(A1+1,B1+1)-1)*(A5-A4)+IF(NETWORKDAYS(B1+1,B1+1),MEDIAN(MOD(B1,1),A5,A4),A5)-MEDIAN(NETWORKDAYS(A1+1,A1+1)*MOD(A1,1),A5,A4))*24