Forum Discussion

thunderthumbs's avatar
thunderthumbs
Copper Contributor
Jan 29, 2019
Solved

Formula Help

Hi,

 I need some help I trying to create a formula between two date / time ranges. The day / time format I'm using is 21/01/2019 08:45:03. So I need to calculate the time difference between two dates and times as below example :

21/01/2019 08:45:03 (Date 1)

1/22/2019 11:01:27 AM (Date 2)


 However there are several exclusions which are :

 The hours I need to calculate are working hours between 07:30 and 18:00 Monday to Friday only. Any times and days outside of this need to be ignored.

 What's the best / easiest way to do this please ?


Also I need to exclude UK bank holidays as well as weekends thanks

 

 

Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 04, 2019

    Hi,

     

    With seconds

    =N("Full workdays between next after Start and previous to End")+
NETWORKDAYS(A5+1,B5-1,HolidayList)*((INT(B5)-INT(A5))>1)&" days "&
TEXT(N("Start and End are not the same")+(INT(B5)<>INT(A5))*
(
(WEEKDAY(A5,2)<6)*((A5-INT(A5))<=$B$2)*($B$2-MAX(A5-INT(A5),$A$2))+
(WEEKDAY(B5,2)<6)*((B5-INT(B5))>=$A$2)*(MIN(B5-INT(B5),$B$2)-$A$2)
)+N("Start and End are the same")+
(INT(B5)=INT(A5))*
((B5-INT(B5))>=$A$2)*((A5-INT(A5))<=$B$2)*
(MIN(B5-INT(B5),$B$2)-MAX(A5-INT(A5),$A$2)),"[h]"" hours ""m"" minutes ""s"" seconds""")

    (format in last string is changed)

     

    Dates in column H are holidays if any. In formula that is named range HolidayList. You may change that in Formulas->Name Manager

     

    or use in formula the range directly like

    ...NETWORKDAYS(A5+1,B5-1,$H$1:$H10)*((INT(B5)-INT(A5))>1)&...

14 Replies