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]

Is that using 9 hours?

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

Don't forget Ctrl+Shift+Enter.

 

[azkhan]

Sorry for being a pain. I am just a basic - intermeditae level user of excel as well as new to this website; therefore I do not know how to attach an excel file to this thread. Could you please provide full formula for the below example in a excel file if possible or just provide the full formula in the thread.?

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)

 

[barry houdini]

Will start and end times always be within working hours? What time is lunch?

 

[Andrew Poulsom]

That would be:

=(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)

confirmed with Ctrl+Shift+Enter.

 

[azkhan]

BTW - I tried the formula you (only the string above and not with the lenghthy one)and it resulted 16 hours.

 

[azkhan]

Well the lunch hour is not fixed for everyone and now i am thinking of taking full nine hours after I realised that it is for TAT calculation and not for payroll. It is fine to ignore the lunch break and consider day to be 9 hours.

 

[azkhan]

Made sure with Ctrl+Shift+Enter. It is counting Fridays and excluding Sat & Sun. We need to exclude Fri & Sat and count Sunday.

 

[azkhan]

Will start and end times always be within working hours? What time is lunch?

Sorry missed the first Q.

Mostly the Start and End fall within the work hours.
<!-- / message -->

 

[barry houdini]

This formula should give you the correct answer assuming start and end times are within the working hours

=(NETWORKDAYS(A1+1,B1+1,INDEX(holidays+1,0))-1)*9+(MOD(B1,1)-MOD(A1,1))*24

format as number

[Note: although it uses NETWORKDAYS the +1 adjustment means that Sunday and Monday are counted as holidays.]

The holidays part is optional (assuming you have a named range called holidays), if you don't want to consider holidays change to

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

 

[Andrew Poulsom]

BTW - I tried the formula you (only the string above and not with the lenghthy one)and it resulted 16 hours.

That's because you didn't confirm it with Ctrl+Shift+Enter instead of just Enter.