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

Topic Labels: Formulas
530 2
cancel
Showing results for
Did you mean:
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
18 - Pluto

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:

``````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

5 - Automation Enthusiast

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)