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 ?

  • Anonymous's avatar
    Anonymous
    Jan 29, 2019

    Hi Sergei,

     

    I am trying to follow this thread as well and tried using your formula but it seems like it's not working for me (or maybe I am doing it incorrectly). I edited the values based on my preference and at the same time removed the holidays. The result I am expecting (based on the example) should be 4 hours and 1 minute as 12-2-2018 fell on a Sunday.

    Can you please check and tell me what am I doing incorrectly?

    Thanks,

    Mir

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 29, 2019

      Hi Mir,

       

      Assumption was Start and End are always within office hours, it looks like that's not your case. I'll check how to correct the formula.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 29, 2019

        Hi Mir,

         

        Not sure I tested all variant, the formula looks like

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

        and in attached file.