asked on

Time difference between dates without weekend

Please help to calculate the time difference:

1. Output needs in minutes
2. Weekend to be removed

I am using below formula provided by Subodh Tiwari (Neeraj) 4 days back but got some error today on one of case.

=IF(OR(E2="",F2=""),"",IF(OR( NETWORKDAYS(E2,F2)-1=1,NETWORKDAYS(E2,F2)=1),"0 "&HOUR(MOD(F2-E2,1) )&" "&MINUTE(MOD(F2-E2,1))&" ",NETWORKDAYS(E2,F2 )-1&" "&HOUR(MOD(F2-E2,1))&" "&MINUTE(MOD(F2-E2,1))&" "))

e.g. 1 - Correct result with above formula
1/4/2017 22:13
1/5/2017 9:58
Result: 0 Days 11 Hour 44 Minutes

e.g. 2 - Wrong result with same formula
2/23/2017 14:30 (E2 cell)
2/24/2017 20:47 (F2 cell)
Result: 0 Days 6 Hour 17 Minutes

Please help.

Thank you,
Chetan Pawar

Shums Faruk

Hi Chetan,

Please try below without Weekends:

=IF(OR(E2="",F2=""),"",DAY(F2-E2)&" Days "& HOUR(F2-E2)&" Hours "& MINUTE(F2-E2)&" Minutes ")

Open in new window

Shums Faruk

You can try below, if you want to consider 0 days, if Start Date & End Date falls on same day.

=IF(OR($E2="",$F2=""),"",IF(TEXT(F2,"d")-TEXT(E2,"d")>1,DAY($F2-$E2)&" Days "&HOUR($F2-$E2)&" Hours "& MINUTE($F2-$E2)&" Minutes ",DAY($F2-$E2)&" Day "&HOUR($F2-$E2)&" Hours "& MINUTE($F2-$E2)&" Minutes "))

Open in new window

PS: Kindly note, its excluding weekend as your subject says.
Calculate-Business-Hours-3.xlsx

Chetan Pawar

ASKER

Hi Shums,

Thanks for the reply.

Please find the attachment , its not excluding weekend. Please correct me if wrong.

Thank you,
Chetan Pawar
Calculate-Business-Hours-3.xlsx

Shums Faruk

Chetan, it is not including weekends. I changed the format to check. From Friday to Monday its counting 3 days.
Please see attached.
Calculate-Business-Hours-3.xlsx

Shums Faruk

Chetan,

Going through your previous post plus this post, I have corrected the formula which will display hours and mins if the difference between start date and end date is less than 1 day. If the difference is more than 1 day, result will display days, hours & mins.

=IF(OR($E2="",$F2=""),"",IF(TEXT(F2,"d")-TEXT(E2,"d")>1,DAY($F2-$E2)&" Days "&HOUR($F2-$E2)&" Hours "& MINUTE($F2-$E2)&" Minutes ",HOUR($F2-$E2)&" Hours "& MINUTE($F2-$E2)&" Minutes "))

Open in new window

Please find attached for your reference.
Calculate-Business-Hours-4.xlsx

Chetan Pawar

ASKER

Hi Shums,

From Friday to Monday, we need output as 1 day - (Friday time: 3h 13 mins and Monday time: 20h 47mins; Total: 24 hr).

Same can work in other formula as attached but not working for all cases.

Please help.

Thank you,
Chetan Pawar
Calculate-Business-Hours-5.xlsx

Martin Liss

What about holidays?

Chetan Pawar

ASKER

Hi Martin,

Actually my manager needs this report on a daily basis and holidays are very less (10 in a year) so as of now ignoring the same. If we can include that will be great but he doesn't want to complicate it more.

Thank you,
Chetan Pawar

Martin Liss

It's easy to ignore holidays. You can use the WORKDAY function

WORKDAY(start_date, days, [holidays])
The first 2 arguments are required and the last one is optional:

Start_date - the date from which to start counting weekdays.
Days - the number of workdays to add to / subtract from start_date. A positive number returns a future date, a negative number returns a past date.
Holidays - an optional list of dates that should not to be counted as working days. This can be either a range of cells containing the dates you want to exclude from calculations, or an array constant of the serial numbers representing the dates.

Shums Faruk

Which means you need to include weekends?

Chetan Pawar

ASKER

No no.. we need to exclude weekend. Holiday means I was saying public holidays (10 days).

Am sorry if I understood it wrongly.

Shums Faruk

From Friday to Monday, we need output as 1 day - (Friday time: 3h 13 mins and Monday time: 20h 47mins; Total: 24 hr).

Here you are saying it must be counted 1 day and

No no.. we need to exclude weekend.

I don't understand? Weekend will exclude Saturday & Sunday. I will not include Holidays.

Chetan Pawar

ASKER

Hi Martin / Shums,

we need to exclude weekend and get the time difference between two dates. Please help.

Thank you,
Chetan Pawar

Chetan Pawar

ASKER

Hi Shums,

Sat-Sun should not be counted.

Shums Faruk

Give me some time to edit the formula

Shums Faruk

Hi Chetan,

Please find attached...
Calculate-Business-Hours-6.xlsx

Subodh Tiwari (Neeraj)

This is getting complex indeed. :)
I think you should have provided a sample workbook with some dates added along with their expected output mocked up manually.

Anyways find the attached with all the formulas you have so far to see which one gives you the desired output.
Calculate-Business-Hours-5.xlsx

Shums Faruk

Chetan,

This is the closest I can get, please check and revert.
Calculate-Business-Hours-6.xlsx

Subodh Tiwari (Neeraj)

Here is another attachment after Shums pointed out an error where both the dates are weekends (I guess the output should be a blank or 0 in that case), the bottom line with yellow color.
Calculate-Business-Hours-6.xlsx

Chetan Pawar

ASKER

Hi Neeraj,

Yes , you are right I should have shared sample document.

In the above sheet, one error in column 7 and 8. Since End date is Mon 10:55 am, output expected is only 10 h 55 min considering Mon hours.

It is comparing with start date time. If I change start date PM to AM, output is changing. Could you please check it once.

Meanwhile I am testing it further.

Thank you,
Chetan Pawar

Subodh Tiwari (Neeraj)

Okay here is my last attempt to tweak this. :)
Column H contains the latest tweaked formula highlighted with yellow.
Calculate-Business-Hours-7.xlsx

Chetan Pawar

ASKER

Hi Neeraj,

Really sorry for showing errors again and again. Please find attached some cases where old formula is showing correct result and new formula doesn't.
Calculate-Business-Hours-7.xlsx

Subodh Tiwari (Neeraj)

Let me know the formula cell references where you are comparing both the formulas.

Chetan Pawar

ASKER

PFA the actual list
Incident-Closed---draft.xls

Subodh Tiwari (Neeraj)

The attached file is of no use unless you let me know where you think the returned output is not correct and also let me know what should be the correct output in those cases (mock it up manually in a blank column next to the formula cells) and upload a small sample workbook again.

Chetan Pawar

ASKER

Hi Neeraj,

Attached the sheet with desired output in Green color compared with New formula in column I. Only mentioned the cases which is not matching with new formula.

Thank you,
Chetan Pawar
Incident-Closed---draft.xls

Subodh Tiwari (Neeraj)

@Chetan
This is my last attempt and if this doesn't produce the desired output, maybe someone else would be able to crack it.
The returns obtained by this formula match your desired output for most of the dates. And where it doesn't match, either your manual calculation is wrong or maybe I am missing something. e.g. E6&F6.

Anyways this was max I could do for you. See if this works.

=IF(OR(E2="",F2=""),"",IF(NETWORKDAYS(E2,F2)=0,"0 Day 0 Hour 0 Minute",IF(NETWORKDAYS(E2,F2)=1,IF(INT(E2)=INT(F2),"0 Days "&HOUR(F2-E2)&" Hours "&MINUTE(F2-E2)&" Minutes","0 Days "&IF(WEEKDAY(E2,2)<6,HOUR(E2)&" Hours "&MINUTE(E2)&" Minutes",HOUR(F2)&" Hours "&MINUTE(F2)&" Minutes")),NETWORKDAYS(E2,F2)-1&" Days"&" "&IFERROR(HOUR(NETWORKDAYS(E2,F2)-1-IF(WEEKDAY(E2,2)>5,0,MOD(E2,1))+MOD(F2,1)),0)&" Hours"&" "&IFERROR(MINUTE(NETWORKDAYS(E2,F2)-1-IF(WEEKDAY(E2,2)>5,0,MOD(E2,1))+MOD(F2,1)),0)&" Minutes")))

Open in new window

Martin Liss

I may be returning to something that was already discussed, but I assume that if the start date were 2/23 of this year and the end date were 2/27 then you would want to exclude 2/25 and 2/26 because they are weekend days. But what days would you want to exclude (if any) if the start date were 2/25?

Chetan Pawar

ASKER

Yes Neeraj, its adding 1 extra day in cases like E6, F6

Hi Martin - If it starts on Weekend, we need to consider start time as Mon 00:00 AM.

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chetan Pawar

ASKER

Hi Neeraj - Best solution sirji :) I haven't seen any issues in this. Thanks a lot..