Help

Assign a Month & Year based on if a date falls in period.

Topic Labels: Formulas
218 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Nicholas_Timms
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello All,

I wish to create an if formula which will return a Month Year result based on if a date falls on or between two dates.

For example, if date A is on or between date X and date Y then return Month Year

Think along the lines of budgeting and expenses:
if Paid Date is 01/02/2024 (Date A)

and because Date A is on/or between 15/01/2024 (Date X) and 14/02/2024 (Date Y) (Budget/Expense Period)

Then return January 2024 (because January period is 15/01 to 14/02)


2 Replies 2

Hmm, the only way I can think of doing this is by hardcoding the formula I'm afraid.  Here's how you could do it for that budget period of 15 Jan 24 to 14 Feb 24:

Screenshot 2024-02-22 at 6.49.13 PM.png

IF(
  AND(
    OR(
      IS_SAME(
        {Date},
        DATETIME_PARSE(
          '15/01/2024',
          'DD/MM/YYYY'
        ),
        'day'
      ),
      IS_AFTER(
        Date,
        DATETIME_PARSE(
          '15/01/2024',
          'DD/MM/YYYY'
        )
      )
    ),
    OR(
      IS_SAME(
        Date,
        DATETIME_PARSE(
          '14/02/2024',
          'DD/MM/YYYY'
        ),
        'day'
      ),
      IS_BEFORE(
        Date,
        DATETIME_PARSE(
          '14/02/2024',
          'DD/MM/YYYY'
        )
      )
    )
  ),
  'January 2024'
)


And so you'd need that chunk of code per period, and you'd need to update it per year I guess?  Or perhaps you could just do the next couple of years at one go

I was trying to think of a way to do it with a new table where you set all the budget periods, but that would require a script and seems more work than it's worth

To be frank, I was asking to see if there was a more eloquent solution, but you have confirmed my suspicion.

The only way I can see this working is if one's budget is per calendar month i.e. January = January and not Date x to Date Y (at least as you say without scripting that is)