Skip to main content

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 😵 and 14/02/2024 (Date Y) (Budget/Expense Period)

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


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

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


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)

Reply


Terms & Conditions