Networkdays with start and end times on different days due to multi country shifts

SteelBird

New Member
Joined
Nov 14, 2019
Messages
2
Hi,






I am faced with this scenario where I need to calculate the number of minutes between two date & time stamps with the Start and End time in different hours . My scenario is as below


Excluding Hours between 7:00 PM to 11:00 PM EST all other time, excluding Saturday and Sunday and Holidays need to be calculated


I am currently using this formula, but it yields incorrect results due to the the start and end times spanning multiple days




=IF(U3="Y",,((NETWORKDAYS.INTL(Q3,R3,1,HolidayRef!$A$1:$A$2)-1)*(TimeRef!$A$2-TimeRef!$B$2)+IF(NETWORKDAYS.INTL(R3,R3,1,HolidayRef!$A$1:$A$2),MEDIAN(MOD(R3,1),TimeRef!$A$2,TimeRef!$B$2),TimeRef!$A$2)-MEDIAN(NETWORKDAYS.INTL(Q3,Q3,1,HolidayRef!$A$1:$A$2)*MOD(Q3,1),TimeRef!$A$2,TimeRef!$B$2))*24*60)


Where Y is an internal control variable and my TimeRef sheet has Start Time as 23:01 and End time as ​19:00


I get incorrect results when the time stamps are in the same day or the next day. Some examples are below




Start Date End Date ElapsedTime
5/31/2019 8:42 5/31/2019 9:49 0.00
5/31/2019 9:49 6/6/2019 9:35 -964.00​


Any help would be greatly appreciated!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,095,866
Messages
5,446,963
Members
405,424
Latest member
fidarbearing

This Week's Hot Topics

Top