if day of month is greater than 15, increment month by 1 in text format

[Wishmaster89]

Hi All

I've got a formula where im trying to increment the month by 1 if the day is >15. The EDATE part works fine on its own but when i add in the text formatting it gives the error of #NAME?

Book1
	A	B
1	Start Date	Onboard date
2
3	18/02/2023	#NAME?
Sheet1

Cell Formulas
Range	Formula
B3	B3	=IF(DAY(A3)>15,TEXT(EDATE(I12,1),mmm-yy),TEXT(A3,"mmm-yy"))

 

[FormR]

Hi, your missing the double quotes around this part mmm-yy of the first TEXT() function.

 

[Wishmaster89]

Thank you @FormR that worked ,

Just realised i referenced the wrong cell in the edate function aswell doh!

 

[RoryA]

Just FYI, you can shorten that a bit to:

=TEXT(EDATE(A3,N(DAY(A3)>15)),"mmm-yy")

DAY(A3)>15 will return True/False as applicable. N() will convert that to 1 or 0 respectively, so it only adds a month if the day is >15.

 

[Dave Patton]

an alternative to post #1

Date and Time 2021.xlsm
	A	B	C
1	Start Date	Onboard date	Onboard date 2
2	18-Feb-23	Mar-23	Mar-23
3	12-Feb-23	Feb-23	Feb-23
4
13d

Cell Formulas
Range	Formula
B2:B3	B2	=IF(DAY(A2)>15,TEXT(EDATE(A2,1),"mmm-yy"),TEXT(A2,"mmm-yy"))
C2:C3	C2	=TEXT(EDATE(A2,0+(DAY(A2)>15)),"mmm-yy")

 

[Phuoc]

Another way:

=TEXT(EOMONTH(A3-15,1),"mmm-yy")