Forum Discussion
Formula Help
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)&...
Hi,
Please check this https://techcommunity.microsoft.com/t5/Excel/Calculating-and-expressing-time-worked-as-days-hours-minutes/m-p/278493 thread, that's a similar task
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?
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
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,
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
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.