Jan 29 2019 12:25 AM
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
Jan 29 2019 03:07 AM
Hi,
Please check this https://techcommunity.microsoft.com/t5/Excel/Calculating-and-expressing-time-worked-as-days-hours-mi... thread, that's a similar task
Jan 29 2019 07:08 AM
Jan 29 2019 07:53 AM
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
Jan 29 2019 10:07 AM - edited Jan 29 2019 10:08 AM
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
Jan 29 2019 10:32 AM
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.
Jan 29 2019 02:53 PM
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.
Jan 30 2019 03:10 AM
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 ?
Jan 31 2019 10:01 AM
Sorry, missed that. You took not-modified formula, the latest one in my previous post and in attached
Feb 04 2019 04:07 AM
Feb 04 2019 04:25 AM
SolutionHi,
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)&...
Feb 04 2019 04:25 AM
SolutionHi,
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)&...