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)
Assign a Month & Year based on if a date falls in period.
+4
+31Hmm, 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
+4Hmm, 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
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)
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.