SOLVED

Formula Help

Copper Contributor

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

 

 

14 Replies
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 ?

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

image.png

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

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?clip.png

Thanks,

Mir

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.

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.

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 ?

Hi Sergei,

It did work! You are brilliant. :)

Many thanks!

-Mir

Mir, so far so good, glad to help

Hi Sergei,

 

Any thoughts or updates on my last note thanks ?

Sorry, missed that. You took not-modified formula, the latest one in my previous post and in attached

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
best response confirmed by thunderthumbs (Copper Contributor)
Solution

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

image.png

 

or use in formula the range directly like

...NETWORKDAYS(A5+1,B5-1,$H$1:$H10)*((INT(B5)-INT(A5))>1)&...
Excellent thanks again all working. Appreciate your help
1 best response

Accepted Solutions
best response confirmed by thunderthumbs (Copper Contributor)
Solution

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

image.png

 

or use in formula the range directly like

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

View solution in original post