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 date...
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)&...
  • thunderthumbs's avatar
    thunderthumbs
    Copper Contributor
    Jan 29, 2019
    Thanks

    OK this formula works out the time difference. How do I exclude weekends (Saturdays, Sundays), bank holidays and any hours outside the working window of 07:30 and 18:00 thanks ?
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 29, 2019

      Did you check the file attached to the last post in that thread?

      NETWORKDAYS calculates only working days in your locale (you may use NETWORKDAYS.INTL for more flexibility), holidays are in column H, office hours start/finish are in A2 and B2

      • thunderthumbs's avatar
        thunderthumbs
        Copper Contributor
        Jan 30, 2019

        Sorry Sergei I missed that. I've taken a copy of that file and made some changes but I do not think the formula is working correctly. I've uploaded a copy of the file and my changes are located in the "Modified" column.

         

        I initially thought this was because cell references A2 and B23 were not absolute but adding the dollar signs in the formula does not help. Any ideas thanks ?

         

        Also can we extend the formula to measure days, hours, minutes and seconds thanks ?