How to set up dates to roll over effective FYE

Estelle Redding

Scenario 1: I need to calculate the total # of projects closed in the previous FY and display a running total of projects closed in the current FY. Right now, I'm changing the FY dates in the summary fields, and I would love to stop having to do this! FYE is 6/30, and my formulae are as follows.

Current FY: =COUNTIFS({ARCHIVE PPR Phase}, "Closed", {ARCHIVE PPR Actual End}, >=[FY 2023 start]#, {ARCHIVE PPR Actual End}, <[FY 2024 start]#)

Previous FY: =COUNTIFS({ARCHIVE PPR Phase}, "Closed", {ARCHIVE PPR Actual End}, >=[FY 2022 start]#, {ARCHIVE PPR Actual End}, <[FY 2023 start]#)

Scenario 2: Renewal dates roll every three years on the Appt Expir Date. The first set of due dates is 6/30/2023, 6/30/2024, and 6/30/2025. These would change on 7/1, so 2023 becomes 2026, 2024 becomes 2027, 2025 becomes 2028, etc. The wrinkle is that they are tied to caveats. The date rolls if a row is flagged for renewal by no later than 6/15. If it isn't flagged, the date stays as is until the next cycle three years later, when the row can be renewed again (it can only be renewed during the renewal period, which is from 3/15 - 5/5 every year). I don't have a formula for this because I don't know where to begin!

I appreciate any guidance on both of these scenarios. Thanks in advance!!!

Genevieve P.

Hi @Estelle Redding

I'll answer your two separate questions in two comments:

1 . I actually really like the idea of having your dates in a summary field - this makes it really easy to adjust / troubleshoot in the future!

However if you want to simply use today's date to figure out the current FY, you can do this by using the TODAY function. Then you can grab the Year from today's date and plug that into a DATE function to specify the start of your FY.

For example, should your FY start in February, you could do something like this:

=IF(MONTH(TODAY()) > 1, DATE(YEAR(TODAY()), 02, 01), DATE(YEAR(TODAY()) - 1, 02, 01))

What this does is it checks if today's Month is greater than January. If it is, it takes the YEAR of today to create the date Feb 1st, today's year as the Start of your FY.

However if today's month IS January, it subtracts 1 from the year to find the previous year for the February start date. You can then substitute your current Summary Field reference with an IF statement, like so:

=COUNTIFS({ARCHIVE PPR Phase}, "Closed", {ARCHIVE PPR Actual End}, >= IF(MONTH(TODAY()) > 1, DATE(YEAR(TODAY()), 02, 01), DATE(YEAR(TODAY()) - 1, 02, 01)), {ARCHIVE PPR Actual End}, < IF(MONTH(TODAY()) > 1, DATE(YEAR(TODAY()), 02, 01), DATE(YEAR(TODAY()) - 1, 02, 01)))

Then for the Previous FY, you would subtract 1 from the year as the first part of your formula, and 2 from the year as your second part (if the date is January):

=IF(MONTH(TODAY()) > 1, DATE(YEAR(TODAY()) -1, 02, 01), DATE(YEAR(TODAY()) - 2, 02, 01))

or:

=COUNTIFS({ARCHIVE PPR Phase}, "Closed", {ARCHIVE PPR Actual End}, >= IF(MONTH(TODAY()) > 1, DATE(YEAR(TODAY()) -1, 02, 01), DATE(YEAR(TODAY()) - 2, 02, 01)), {ARCHIVE PPR Actual End}, <IF(MONTH(TODAY()) > 1, DATE(YEAR(TODAY()) -1, 02, 01), DATE(YEAR(TODAY()) - 2, 02, 01)))

Does that make sense? Here are some articles that may help: DATE Function, TODAY Function, YEAR Function

Genevieve P.

@Estelle Redding

For your renewal dates, scenario 2:

In this instance I would recommend having only part of this automated. I would suggest using Update Requests during the renewal period, where if the row is flagged for Renewal it sends an Update to someone to confirm the first renewal date.

In your example: The first set of due dates is 6/30/2023, 6/30/2024, and 6/30/2025.

In the Update Request, you would ask for a change to "6/30/2023" to the new start date > "6/30/2026"

Then you can use a formula to +1 or +2 years on to that date for your second and third dates:

=[First Due Date]@row + 365

=[First Due Date]@row + 730

See: Use Formulas to Perform Calculations With Dates & Automatically request updates on tasks

I hope that helps!

Genevieve

Estelle Redding

Thanks, Genevieve; I may put your second formula in the FYE summary field and leave the cross-reference in the sheet. It will undoubtedly make the formulae easier to navigate!

I'll parse my way through your response to the 2nd scenario. Another twist:: not only are the expiry dates different based on the program, but also the location. My source (INDEX) sheet lists the programs and their initial renewal dates based on location. My intake sheet pulls the data in from the source sheet, and therefore first must determine the program and then the location (or vice versa). Some programs exist in two locations, while some exist only in one.