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
Feb 04, 2019
Thanks Sergei

Sorry for delayed reply. Looks good. Two final questions :

1. Can you update formula to include seconds ?

2. What do the dates in column H refer to ?

Many thanks
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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
    Feb 05, 2019
    Excellent thanks again all working. Appreciate your help