2

I'm not very experienced with google sheets formulas, and I'm attempting to create a rolling year formula for a scholarship spreadsheet. All I really need it to do (if it's even possible) is to automatically update the year once the date listed in a column has passed. For example, if the date is 2/1/2022 and that date has passed it just needs to update to 2/1/2023. EDATE has seemed like the most effective solution for this, but it would have to be manually done once a year and I will no longer be working in this position by next year and won't be here to update it myself. So I am attempting to automate the sheet so I won't have to leave instructions on just how to update the dates. This is for ease of use and so that the sheets can be continued to be used without having to manually update 100s of dates every year. I appreciate any and all suggestions!

This is a link to the copy of the spreadsheet in case it's helpful: https://docs.google.com/spreadsheets/d/1h4EaqqwvqszvHt3X1Agm-8gLIjbgPgh8OMDizi7ZDnY/edit?usp=sharing

Improve this question
1
  • Just create a single field on the sheets (or drop list) where they input the current year and have your dates work in relation to that field.
    – Blindspots ♦
    Nov 3, 2022 at 20:34

1 Answer 1

Reset to default
0

Mmm, yes, if there is an option to insert a column for day and a column for month, it would be easy to create a formula like this:

=IF(DATE(YEAR(TODAY()),B2,C2)>TODAY(),DATE(YEAR(TODAY()),B2,C2),DATE(YEAR(TODAY())+1B2,C2)) --> You could also do it as an array formula

If you need it to do it all in one cell, you could do something like:

=IF(DATE(YEAR(TODAY()),4,10)>TODAY(),DATE(YEAR(TODAY()),4,10),DATE(YEAR(TODAY())+1,4,10)) --> replacing 4 and 10 each time you need to change month and day

But, in order to just change once month and day and make it more "accesible" and less propense to mistakes, you could make it a LAMBDA function like this:

=lambda(dd,mm,IF(DATE(YEAR(TODAY()),mm,dd)>TODAY(),DATE(YEAR(TODAY()),mm,dd),DATE(YEAR(TODAY())+1,mm,dd)))(4,10)

And only manually input that final (4,10) with your day and month between parenthesis and it will apply them in all the rest of the formula

As a final step you could create a "Named function" with this prior lambda function. It would be just a luxury XD

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.