Formula for weekdays

[IKHAN]

Hi ,

Any help ?

Looking for formula to extract ONLY Off hours between 2 given dates and times

Off hours Weekdays Mon 5pm to next day 9am
Off hours Weekend (Friday- 5pm to 9am Monday)

format of dates: m/d/yyyy h:mm

See attached file for ref.

 

[David Evans]

Take a look at this - it should give you the basis for your calculation

http://chandoo.org/wp/2010/09/10/working-hours-formula/

I realize you're searching for Non-working hours, but Elapsed Time - Working hours should give you Non-Working hours?

 

[IKHAN]

Hello ,

Couldn't find needed in above link.

Need help with a FORMULA to extract Total number of OFF business hours from provided start and end time.

Weekday OFF business hours (mon -Fri ) 5pm - 9am
Weekend OFF business hours = Friday 5pm to monday 9AM

Test file attached

 

[IKHAN]

Came across this formula,

Can this formula be converted to use DATEIFF instead of Networkdays.

Networkdays days doesn't work in THIRD PARTY APPLICATION

=ROUND((NETWORKDAYS(A3,B3)*8)-IF(WEEKDAY(A3,2)>=6,0,MIN(MAX(24*(MOD(A3,1)-"9:00"),0),8)) - IF(WEEKDAY(B3,2)>=6,0,MIN(24*MAX(("17:00"-MOD(B3,1)),0),8)),2)