Date calculation ignoring a date?

AlFresco

What's a good idea for calculating backwards from a date counting all days (including non-workdays) and ignoring a date (we can call it a holiday if we like since the workday function calls it that).

I.e. is there any other date calculation function that has the "Holiday" parameter besides Workday?

Paul Newcome

Why is it that you can't use the WORKDAY function? Knowing this will help with a solution.

AlFresco

Weekends do matter for most of the sheet.

But some of the columns have a requirement that's just a straight-up 6 weeks beforehand. And 4 weeks beforehand, etc.

That's a good question; I might be thinking about this wrong.  But I think 6 weeks beforehand is different than 6 weeks counting only weekdays.

Paul Newcome

6 weeks beforehand is different than 6 weeks counting only weekdays

 

Really it depends on how your calculations are made. Taking the above statement literally does generate a difference because you are calculating 42 days vs 42 working days.

 

On a calendar that would be 6 weeks vs 8 weeks and 2 days.

.

If you are able to provide some screenshots and a more detailed explanation of how everything is working/how you want things to work, we may be able to help with a good solution.

The functions of NETWORKDAY(S) also account for holidays, and there are a few other workarounds, but the best solution is going to depend on your details.

AlFresco

Yup that's why I'm posting!

Would like to calculate 6 weeks back (not 6 weeks of workdays) from a column.

Based on that alone of course it's easiest just to:

=[Some Column]8 - 42

But add in the requirement that we'd like to ignore a day in the calculation.

WORKDAY has a nice Holiday parameter to ignore days.

=WORKDAY([A calculated column]9, -42, [Avoid]1)

But of course that won't work because it's work days that's being counted.

 

So my question is how to do the above without using WORKDAY.

Paul Newcome

Ok.. I just want to make sure I am clear on this. Basically this is what you are wanting. I'll use year days (jan1 = 1, feb 1 = 32, mar 1 = 59 or 60 depending on leap year, etc...) here just for simplicity instead of trying to manually calculate specific dates.

 

If your start date is 180, you want to go back 6 weeks (42 days). But if there is one holiday, you are actually wanting to go back 43 days?

 

So no holidays would give a result of the date for 138 and one holiday would give the date for 137.

 

Is this correct?

AlFresco

Yes! (As a side note–I love your example and wish the Function definitions would include examples with dates.)

Paul Newcome

The easiest way to go about this would be to build out a table of dates you want to skip. Just one date type column somewhere (I will call it [Date Help] in this example) that lists out all dates you want to skip over.

 

To build this, we will break it down into sections...

 

First we establish the initial date:

=Date@row - 42

 

Then we need to find however many days fall between the two dates:

=COUNTIFS([Date Help]:[Date Help], AND(@cell <= Date@row, @cell >= Date@row - 42))

 

Now we just subtract that count from the date we initially established.

 

=Date@row - 42 - COUNTIFS([Date Help]:[Date Help], AND(@cell <= Date@row, @cell >= Date@row - 42))

.

NOTE: If this calculation pushes that date far enough back to overlap another date on the holiday sheet, those new dates that fall within the range will not be included within the calculation. 

 

You would need to run it again by taking the final formula and tacking on another COUNTIFS with the new date included in the criteria which can get messy. 

 

The reason I did not do this is because technically (depending on your start date and how many dates you want to skip over there are and how close together they are), this could take many many many runs through the calculations. If you would like to run this through a couple of times instead of once, let me know. I may have a solution for that as well.

AlFresco

Another note for future searchers: I'd mistakenly thought I could use a "start" date and "end" date and then reference it using a range in the WORKDAY Holiday parameter.

For example:

01/01/19

01/05/19

But I believe we have to list out all the dates when referencing. I.e.

01/01/19

01/02/19

01/03/19

01/04/19

01/05/19

Paul Newcome

You are correct that this solution requires all dates to be listed out individually. There is a way to do it where you can specify a start and end date, but I would have to do a little more playing around to get you that solution. Depending on the rules for the dates you are skipping over, we may even be able to automate those too.

 

I have a template I built where I do something similar to this, but it is moving forwards as opposed to backwards. I'll let you know what I can come up with if you are interested in this other solution.