Future Date Calculation to a nth Day of Month

PEvansLC · 04/06/24

Hello, looking for help with this formula. Need to calculate a future date based on the [End Date]

Conditions:

(1) Only display a future date if [Ord Amt] is true;

(2) Future date to be one month earlier then month of [End Date] unless;

(3) [End Date] month = January, then Future date is to be December and one year earlier than [End Date]

(4) All future dates are to calculate to the 2nd Tuesday of the Future date MONTH/YEAR

The formula I have works on all dates other than when the Future date is December. Those Future dates, the DAY always displays as “1” and is not calculating to the 2nd Tuesday of that December/YEAR. I've looked at this too long to see the error. Many thanks in advance.

=IF([Ord Amt]@row = true, IF(MONTH([End Date]@row) = 1, DATE(YEAR([End Date]@row) - 1, 12, 1) + IF(WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 1, 0, 7), 0), DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1) + IF(WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) <= 3, IF(WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) = 3, 7, 10 - WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1))), 17 - WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1))) + IF(WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) = 1, 0, 7), 0)), "")

ro.fei · 04/07/24

What a monster formula, this is very impressive!! Super easy to get lost in formulas this big, but I think I may have found the issue. For clarity's sake, I'm going to use this formatting to indicate which part of the formula I'm referring to below.

This is the portion of the formula that runs when the End Date month is January:

=IF([Ord Amt]@row = true, IF(MONTH([End Date]@row) = 1, DATE(YEAR([End Date]@row) - 1, 12, 1) + IF(WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 1, 0, 7), 0), DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1) + IF(WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) <= 3, IF(WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) = 3, 7, 10 - WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1))), 17 - WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1))) + IF(WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) = 1, 0, 7), 0)), "")

Let's pull this out separately. The first portion below returns December 1 of the previous year:

DATE(YEAR([End Date]@row) - 1, 12, 1) + IF(WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 1, 0, 7), 0)

Then you have an IF statement to determine how many days to add to the December 1 date:

DATE(YEAR([End Date]@row) - 1, 12, 1) + IF(WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 1, 0, 7), 0)

Let's separate this out even more to look at only the portion to determine how many days to add. Right now, the logical test checks if the December 1 date is a Tuesday:

IF(WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 1, 0, 7), 0)

Then you have another "inner" IF statement in the value if true position of the "outer" IF statement. This inner IF statement checks whether the December 1 date is a Sunday (logical test formatted below):

IF(WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 1, 0, 7), 0)

The inner IF statement then returns 0 if it's a Sunday & 7 if it's not (entire inner IF statement formatted below):

IF(WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 3, IF(DAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) = 1, 0, 7), 0)

The inner IF statement is acting as the value if true for the outer IF statement, & the 0 at the end is the value if false for the outer IF statement. That means this little portion of the formula is doing the following:

-Check if the December 1 date is a Tuesday

-----If it ISN'T a Tuesday, add 0 days

-----If it IS a Tuesday, check if it's a Sunday

---------If it IS a Sunday, add 0 days (never occurs because it can't be both Tuesday & Sunday)

---------If it ISN'T a Sunday, add 7 days (meaning 7 days is always added if it's a Tuesday, but only after it also checks if it's Sunday)

I haven't taken a look at the rest of the formula & I don't have the fixed one yet, but this should get you started finding the issue. I feel like this is a puzzle hahah I'll probably work on this a bit to see if I can come up with a fixed version for you but I hope this helps!!! This is a really tough one, you should be super proud 😊

ro.fei · 04/07/24

Okay I think I got the conditions nested how you need them!! I also simplified some of what you had, but you were most of the way there already. Here's what I've got:

=IF([Ord Amt]@row = true, IF(MONTH([End Date]@row) = 1, DATE(YEAR([End Date]@row) - 1, 12, 1) + IF(WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)) <= 3, 10 - WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1)), 17 - WEEKDAY(DATE(YEAR([End Date]@row) - 1, 12, 1))), DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1) + IF(WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)) <= 3, 10 - WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)), 17 - WEEKDAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row) - 1, 1)))))

I think it covers everything you wanted, here are the basics:

First checks if Ord Amt is true
If End Date is in January, the Projected Date calculates the first day of December of the prior year plus X days to make sure it's the second Tuesday of the month
If End Date isn't in January, the Projected Date calculates the first day of the previous month in the same year plus X days to make sure it's the second Tuesday of the month

To calculate the X number of days to add, it uses the WEEKDAY() value of the intermediate date (calculated within the formula) & the necessary constant:

If WEEKDAY() <= 3, adds 10 days & subtracts the WEEKDAY() value
If WEEKDAY() >3 adds 17 days & subtracts the WEEKDAY() value

I made a test sheet with your same dates to show the outputs (also including what all the dates would be if the Ord Amt column was checked off for everything):

Super cool formula, took me a minute to get my footing on this one. It's so easy to get lost in these long formulas, especially without CountIFS & nested IF statements required so often. You should be proud!

I hope this is helpful but don't hesitate to @ me if you have any questions at all!! 🌻

ro.fei · 04/07/24