Forum Discussion

Copper Contributor

Nov 17, 2021

Solved

Determine if a date belongs in a certain month

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 tr...

excel

Formulas and Functions

Riny_van_Eekelen
Nov 17, 2021
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))
Book10.xlsx9 KB

Riny_van_Eekelen

Platinum Contributor

Nov 17, 2021

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

Book10.xlsx9 KB

BigDaveB
Copper Contributor
Nov 17, 2021
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
- Riny_van_Eekelen
  Platinum Contributor
  Nov 17, 2021
  BigDaveB Glad it worked out. By the way, I just added another formula to my original post. It's a little bit more fancy 🙂