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.

 

[Eric W]

Welcome to the MrExcel forum!

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

Book1
	Y				AC
1	AFS (Non-Binding)/F
2	Existing stock depleted				TBC
3	AFS 2/3/2021				4/3/2021
Sheet1

Cell Formulas
Range	Formula
AC2:AC3	AC2	=IFERROR(EDATE(AGGREGATE(14,6,RIGHT(Y2,{8,9,10})+0,1),2+(DAY(AGGREGATE(14,6,RIGHT(Y2,{8,9,10})+0,1))>=15)),"TBC")

Also, please check out the XL2BB tool to show a sample of your sheet. I just used it, and it makes it much easier to work with your sample data. You can check the link in my signature, or in the reply box.

 

[Peter_SSs]

Welcome to the MrExcel board!

As well as using XL2BB as suggested by Eric, providing a slightly larger data sample and expected results would help so that we can assess any variety in your data.

So, like Eric, assuming any data is on the right, IF you have the LET function in your 365 version, you could also try this

20 12 29.xlsm
	Y	Z	AA	AB	AC
1	AFS (Non-Binding)/F
2	Existing stock depleted				TBC
3	AFS 2/3/2021				2/05/21
4	AFS 22/3/2021				22/06/21
Check Month

Cell Formulas
Range	Formula
AC2:AC4	AC2	=LET(dte,TRIM(RIGHT(SUBSTITUTE(Y2," ",REPT(" ",10)),10))+0,IFERROR(EDATE(dte,2+(DAY(dte)>15)),"TBC"))

 

[Bigbanana]

The date in Y column was in DDMMYYYY format. I've tried the formula:
=IFERROR(EDATE(AGGREGATE(14,6,RIGHT(Y2,{8,9,10})+0,1),2+(DAY(AGGREGATE(14,6,RIGHT(Y2,{8,9,10})+0,1))>=15)),"TBC")

and the result was Apr'2021 instead of May'2021.

Pls advise what went wrong?

 

[Peter_SSs]

Pls advise what went wrong?

Hard to work out what is wrong from looking at a picture.
Eric's formula works for me.
Did you try the other suggested formula?

Try taking a fresh worksheet, click this icon

on Eric's or my mini-sheets and paste into cell Y1 on your fresh sheet and see what happens.

If still a problem, can you give us a 5-10 rows of sample data with XL2BB so that we can be sure we are testing with the same data that you have?

 

[Eric W]

The date in Y column was in DDMMYYYY format. I've tried the formula:
=IFERROR(EDATE(AGGREGATE(14,6,RIGHT(Y2,{8,9,10})+0,1),2+(DAY(AGGREGATE(14,6,RIGHT(Y2,{8,9,10})+0,1))>=15)),"TBC")

and the result was Apr'2021 instead of May'2021.

Pls advise what went wrong?

I figured out that the date in column Y was in DD/MM/YYYY format from your formulas. However, it's a date embedded in a text value. Both my formula and Peter's extracts the text date, and use Excel to convert it to a numerical date value, by adding 0. However, when Excel converts the date, it uses the Regional Settings from the Windows Control Panel. What do you have the date format set as there? Make sure that it's set to DD/MM/YYYY. If you want to keep the regional settings as is, then we'd need to extract each part of the date like you did, which would make for a much longer formula. Peter could probably do better using LET.

 

[Peter_SSs]

If Eric is right about the Regional settings issue then this could be tried (though as yet we don't know if the OP has the LET function )

20 12 29.xlsm
	Y	Z	AA	AB	AC
1	AFS (Non-Binding)/F
2	Existing stock depleted				TBC
3	AFS 2/3/2021				02-May-2021
4	AFS 22/3/2021				22-June-2021
Check Month

Cell Formulas
Range	Formula
AC2:AC4	AC2	=LET(ex,RIGHT(SUBSTITUTE(SUBSTITUTE(Y2,"/"," ")," ",REPT(" ",10)),30),dte,DATE(RIGHT(ex,10),MID(ex,10,10),LEFT(ex,10)),IFERROR(EDATE(dte,2+(DAY(dte)>15)),"TBC"))

 

[Bigbanana]

Thanks both. Indeed, it is the regional date setting that is messing things up. Once I change the regional date setting to DDMMYY, it works fine.

My next question is that I cannot control users' regional date setting. I think the safer bet will be to revise the formula to a longer one?

Hope you can advise!

Thanks.

 

[Peter_SSs]

Hope you can advise!

I did. Have you tried it?

 

[Bigbanana]

I tried this, =LET(ex,RIGHT(SUBSTITUTE(SUBSTITUTE(Y2,"/"," ")," ",REPT(" ",10)),30),dte,DATE(RIGHT(ex,10),MID(ex,10,10),LEFT(ex,10)),IFERROR(EDATE(dte,2+(DAY(dte)>15)),"TBC"))

Did not work