I am using the Google Analytics Add-On for Google Spreadsheets, and need to get data for the financial year to date. I have the finish date set as:


Which is working fine. Now I need to have a start date that is the 6th of April prior to today. I can enter this manually, but was wondering if there was a way of doing it using a formula working backwards from TODAY?

1 Answer 1


The following formula does this:

=if(date(year(today()), 4, 6) < today(), date(year(today()), 4, 6), date(year(today())-1, 4, 6))

It constructs date(year(today()), 4, 6), which is the 6th of April of the current calendar year. If this date is prior to today, it is the output. Otherwise, the year is reduced by 1.

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.