Nested Function Issue

I am trying to set up a formula that will calculate with Networkdays() the differences between two dates with times. I need the formula to first check if the end date is a weekend or holiday (OT) and if so, just subtract the date and time (H2-G2), but if the end date is not a weekend or holiday, apply the Networkdays formula. The attached example is the exact current formula I have, which should work, however, I noticed while watching the calculation steps under the "Evaluate Formula" tool that it does register a false for the dates, however, it ignores it. I am beginning to think this may be due to me using a named range for the formula, but I cannot be sure.

Needs:
Formula in attachment to check End date for weekend/holiday per the list included.
If End date is Weekend/Holiday, subtract start date from end date and return total number of hours/minutes between the two.
If Start Date is holiday and end date is weekend, subtract start date from end date and return total number of hours/minutes between the two (This applies to holidays that fall on a Friday as some people will work OT on the following Saturday to make up hours instead of using PTO.)
If none of the above apply, apply Networkdays() formula as listed.


This is being used to calculate the total processing time for emails being received and responded to.

Originally Posted by bebo021999

Could you manually input few representative cases with expected outcome?
i.e
case 1: 25/12/2020 - 26/12/2020 === > expected result 1
case 2: 26/12/2020 - 31/12/2020 === > expected result 2
....

Yes.

In our current tracker that is being phased out for this new one, if you enter timestamp A as 11/5/2020 15:09 and timestamp B as 11/9/2020 9:53 you would get 90:44:00. This includes both Saturday and Sunday. It should show as 42:44:00 (at least that is the goal).

Now, for my attached example, if you enter in Date A as 01/01/2021 18:45 and enter Date B as 01/02/2021 9:47 you get a VALUE error as both the start and end dates are excluded from the calculation. (01/01/2021 is a Friday and listed holiday, 01/02/2021 is a Saturday and not counted in the NETWORKDAYS() formula). The formula should, in this instance, count the actual difference between these two timestamps (Result of 15:02:00). Essentially, I need a way to identify that if timestamp B is a weekend (or holiday from the list, though we are closed those days) the formula will calculate the actual time difference between the dates versus omitting the dates based on the normal NETWORKDAYS ruleset. I guess the easiest most exact way to say it is that I am needing to program in an exception to the normal NETWORKDAYS function. Also, since we are using this spreadsheet in office365 online, using a macro for this end is not possible.

I need something that does =if("EXCEPTION RULE","timestamp b" - "timestamp a",NETWORKDAYS(formula))

I have tried it with an if(and( as well as an if(or( statement, but excel isn't registering that a single "False" in the formula for a named range (weekend or holidays) should render the whole statement as false and returns it as true instead. I'm sorry if I'm not being very clear with explaining this.

I have updated my example with what I am currently using (No exceptions), what I my idea was (Exception) and what the value should be if the exception works properly. Please let me know if I can better explain this.

Originally Posted by bebo021999

I interpret your logic as following. Correct me if I went wrong.

let say holiday/weekend : HW ; start date: S; end date: E

case 1: S=HW or E=HW (at least one condition meets)
=E-S

case 2: S<>HW and E<>HW (both meet)
=networkday(S,E,holiday)

If yes, try:

* At least 1 date (S or E) is HW

Please Login or Register  to view this content.

* Alternative solution, similar, both dates are not HW

Please Login or Register  to view this content.

Note: you tried: "ROUNDDOWN(C3,0)<>Holidays" to say C3 is not holiday, it is incorrect, because holidays is a range, it will returns a range of true/false, not a single result. Use countif intead.

This appears to work perfectly. I greatly appreciate the added insight on why my attempt failed.

Ok. I thought that it worked, but when I ran a test with the start date on a non-holiday week date and the end date on a weekend, it didn't skip the holiday, which would still be needed. I am starting to think instead of doing a direct D6-C6 I will need to use two additional If() functions to finish this off. One to test if the end date is a saturday, and if so, use networkdays.int() counting Saturday as a work day and still omitting the holidays, and if not, to check if it's sunday and use networkdays.int() counting Sunday as a work day while still omitting the holidays. Would it be possible to make this addition? I have included a more realistic example of the spreadsheet with this exact situation as the example listed. The two values should be the same in this example, but they are not. As you will see from this example there is alot that goes into this spreadsheet so that may also play a part in this as the total size of the tracker cannot exceed 50mb per office.com's size limits. The workbook attached contains the references page used globally for the workbook as well as the first week of the larger of the actual spreadsheets. This formula is filled from cell E6 through the other weeks on all pages. For this example I have tried both iterations of the example formula provided.

Specs are everything. I'm going to interpret this as the following.

Workdays are nonholiday M-F. Nonworkdays are holiday M-F and all Sa-Su. Start and end each have 3 cases: 1) workday, 2) M-F holiday, 3) Sa-Su.

a. If BOTH start and end dates are on workdays, use NETWORKDAYS between the day after start and the day before end and add times of day start to midnight and end from midnight.

b. If end date is a nonworkday, use simple subtraction.

c. If start date is a M-F holiday AND end date is Sa-Su, use simple subtraction. This seems an unnecessary spec since b would still hold.

?. This leaves it unclear how to handle start date isn't a workday BUT end date is a workday. Do you want to include start's time component or not? If so, then start not a workday would be treated the same as end not a workday. Otherwise, it'd be whole workdays from the day after start to the day before end plus the time portion on end.

case	end 1	end 2	end 3
start 1	a	b	b
start 2	?	b	b,c
start 3	?	b	b

I'm going to treat ? as INVALID.

Formula:

Please Login or Register  to view this content.

Originally Posted by JeteMc

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Ok. I have tried this one and it seems to work properly in all instances I can think of. You rock!

Started using the new tracker with this formula today and there is one hiccup I see. If the start date is a weekend, and the end date is a weekday, it should count as though the start date is within the last 24 hours. Example: If received at 10am on Saturday, and ended at 9:58 am on Monday, it should calculate the time as 23:58:00. Currently, it is calculating it as 47:58:00. I think this can be corrected with an additional if/then statement, but I'm not sure why the current one doesn't work this way other than that the networkdays.intl lists a 7 day work week with only holidays being excepted. When I change the weekend code to 1000001 it corrects it. I think listing the rules below will make more sense.

A) Only calculate if end date (Column D) is not blank.
B) If Start and End are both M-F and not holiday, use networkdays.
C) If Start date is Weekend or Holiday, and End date is M-F not holiday, use Networkdays.intl to calculate difference between end and start date excluding weekends (SA-SU) and holidays.
D) If Start is M-F and not holiday and End is a weekend or holiday, use simple subtraction.

Current Formula:

Please Login or Register  to view this content.

Thanks. I thought I had.

Edited per your request.

This works except for instances where both start and end dates are weekends or a start on a holiday and end on a weekend. As an example, a start timestamp of 12/26/2020 0:00 and an end timestamp of 12/26/2020 9:00 results in a total of 33:00:00 hours, rather than the expected 9:00:00.

Hello! Coming back to this one since something changed and I'm not sure what it is. The department that this is for now works on Saturdays, but if they are responding to an email on Saturday that came in on Thursday, it still ignores Saturday completely. I am still using the formula below as recommended by JeteMc before (different cell references, of course) but if the starting timestamp is 5/5/2022 17:03 and the ending timestamp is 5/7/2022 11:22, then it returns a difference of 18:19:00, where it should be a difference of 42:19:00. A screenshot of this is attached. Due to a systems change, I cannot upload a new copy of the spreadsheet, however, the previous one should still be attached to the previous post.

Formula:

Please Login or Register  to view this content.

Minor change with massive results. Thank you!

I have noticed some inconsistencies in how this calculates as seen in the attached screenshot. The formulae below are for the middle two calculations.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Wait, I think I know why. With these formulae, they are set to have the weekend as sunday only, but in my internal listing of weekends, i have both saturday and sunday listed. Am I understanding this correctly that removing the COUNTIF(Weekends,INT({Cell Ref}) sections may correct this issue?

So the solution for this was to define a new named range for Sundays and replace Weekends with Sundays in the formula resulting in this formula which works correctly.

Formula:

Please Login or Register  to view this content.