Determine if a date belongs in a certain month

[BigDaveB]

Hello all. I am new to this so hoping I am in the right place. Unfortunately I have no examples to share as I can't even begin to build a formula for what I am trying to do.

So here goes. I am trying to build a formula that will determine if a specific date belongs in one budget month or another. For context, this is to build an excel sheet for a home budget. I get paid on the 28th of every month, so my budget month runs from the 28th of this month, to the 27th of the following month, and is named as the following month. So when I get paid on 28 November, that will be the budget month for December.

What needs to happen, is when I set the date of a Income/Expense, the formula needs to work out, in which budget month it will be received / paid.

As an example.

I pay my Car Insurance on 27 November 2021

I get paid on 28 November 2021.

I pay my rent on 29 November 2021

What I need the formula to do:

• Report that the Car Insurance is paid in the budget month of November 2021
  • Budget month 28 October to 27 November
• Report that I get paid in the Budget month of December 2021
  • Budget month 28 November to 27 December
• Report I pay my rent in the budget month of December 2021
  • Budget month 28 November to 27 December

Hoping there is a way to do this. I can if need be use an XLSM file, with a Customer Function.

The final result needs to be in the format of "YYYY-MM" (2021-11 or 2021-12) This is for sorting and filtering purposes. I can probably do this with =TEXT((The Formula), "YYYY-MM")

Many Thanks

[#2 · Accepted solution]

@BigDaveB That could be:

=IF(DAY(A2)>=28,EOMONTH(A2,1),EOMONTH(A2,0))

where the date to check is in A2.

Custom format the cell that holds this formula as yyyy-mm

Example attached. 

Edit: This one will work also:

=EOMONTH(A2,QUOTIENT(DAY(A2),28))

[#3]

@Riny_van_Eekelen 

Hi Riny,
Thanks so much for that. I wasn't even close to that and was trying to build nested If (OR) statements that were long and complicated. But your formula hit the nail on the head.
I made one small change to allow for sorting and filtering of the column to suit my needs

=TEXT(IF(DAY(A2)>=28,EOMONTH(A2,1),EOMONTH(A2,0)),"yyyy-mm")

Again, thank you so much for this.

Dave

[#4]

@BigDaveB Glad it worked out. By the way, I just added another formula to my original post. It's a little bit more fancy :)