dategoogle-sheetsgoogle-sheets-formulaarray-formulas

How do I write a formula in Google Sheets to find the current period in a fiscal year depending on today's date?


I'm trying to write a formula that determines the fiscal year period based on a July 1 start date. I would like quarter intervals for each period to signify which week of the month for the period as well. For example, right now it gives me period 11 because the month is May but it's technically period 10.25 at this point in time. It will truly be period 11 on May 30. I've attached an image of what I've tried so far.

formula screenshot


Solution

  • What ended up working (at least for what I need) is actually using =DATEDIF(A11, today(), "D") and then dividing that number of days by 30.4347.

    I got the 30.4347 by dividing 365.25 (ignoring centurial years, (365+365+365+366)/4 = 365.2425) by 12 months.