Extract month from text and include condition to get updated months

[Bigbanana]

Dear All,

I have a file where I need to extract the month from Column Y's text, which I have used Column AC for that.
If the date in AC is on or before 15, the expected month of revenue = AC + 2 months. If false, = AC + 3 months. I have a Column AD for the results.
If Column Y does not contain any date, the results should appear as TBC.

This works fine, but I need 2 columns (AC and AD) for that.

I wish to combine it into a single column AC, but could not get the formula right.

Any help would be appreciated.

Thanks in advance.

 

[Peter_SSs]

Did not work

Not much to go on.
- Wrong result? (what result for sample data in row in post #7?)?
- Error? (what error?)
- Something else? (what?)

Do you actually have the LET function?

IF you have the LET function in your 365 version,

 

[Peter_SSs]

Is the date always on the right? If so, try:

like Eric, assuming any data is on the right,

Also, we have been guessing that if there is a date, it is at the right of the text. You haven't advised whether that is the case or not so we may be wasting our efforts?
Remember you are very familiar with your data but we only know what you show and tell us.

 

[Bigbanana]

Also, we have been guessing that if there is a date, it is at the right of the text. You haven't advised whether that is the case or not so we may be wasting our efforts?
Remember you are very familiar with your data but we only know what you show and tell us.

Sorry for missing it. If there is a date, it will be on the right of the text.
I do not have the "Let" formula in my Excel.

 

[Dave Patton]

I included most of the suggestions. My system has International Regional Settings dd-mmm-yy.
An additional alternative is Data Text to Columns.

T202012c.xlsm
	A	B	C	D	E	F	G	H	I
1				Subsitute	Aggregate	Long with	V() with	Text to
2		Mid	with V()	with V() Peter_SSs	with V() Eric W	Date	Date	Column
3	AFS 2/3/2021	2-May-21	2-May-21	2-May-21	2-May-21	May 2, 2021	2-May-21	Mar 2, 2021	May 2, 2021
4	AFS 15/3/2021	15-May-21	15-May-21	15-May-21	15-May-21	May 15, 2021	15-May-21	Mar 15, 2021	May 15, 2021
5	AFS 22/3/2021	22-Jun-21	22-Jun-21	22-Jun-21	22-Jun-21	Jun 22, 2021	22-Jun-21	Mar 22, 2021	Jun 22, 2021
6	AFS	TBC	TBC	TBC	TBC	TBC	TBC
7								Text to Columns
2d

Cell Formulas
Range	Formula
B3:B6	B3	=IFERROR(EDATE(--MID(A3,FIND(" ",A3)+1,31),2+(DAY(--MID(A3,FIND(" ",A3)+1,31))>15)),"TBC")
C3:C6	C3	=IFERROR(EDATE(V(--MID(A3,FIND(" ",A3)+1,31)),2+(DAY(V())>15)),"TBC")
D3:D6	D3	=IFERROR(EDATE(V(--TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",10)),10))),2+(DAY(V())>15)),"TBC")
E3:E6	E3	=IFERROR(EDATE(V(AGGREGATE(14,6,RIGHT(A3,{8,9,10})+0,1)),2+(DAY(V())>15)),"TBC")
F3:F6	F3	=IFERROR(EDATE(DATE(RIGHT(A3,4),MID(A3,LEN(A3)-5,1),MID(A3,LEN(A3)-8,2)),2+(DAY(DATE(RIGHT(A3,4),MID(A3,LEN(A3)-5,1),MID(A3,LEN(A3)-8,2)))>15)),"TBC")
G3:G6	G3	=IFERROR(EDATE(V(DATE(RIGHT(A3,4),MID(A3,LEN(A3)-5,1),MID(A3,LEN(A3)-8,2))),2+(DAY(V())>15)),"TBC")
I3:I5	I3	=EDATE(H3,2+(DAY(H3)>15))

Since you do not have LET, you could try a UDF called V(). It has been available for about 20 years.
It saves a Value; much like saving a constant or amount in memory with a calculator.

Public Function V(Optional vrnt As Variant) As Variant
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Try the suggestions and advise advise which work for you.

 

[Bigbanana]

Guys, this worked massively well!

Thanks for the contribution, and I sure learned alot from this.

Take care!

 

[Bigbanana]

Assuming with Dave's data and post above, I would want to take it further by creating a simple Pivot to sum by Month. Understand that Pivots will be unable to Group by month if there are blanks or text in the columns. is there a way to overcome that? I really have some data that I want to indicate as "TBC" for example.