Formula to extract first working day after month end in Cell A1

[howard]

I have text in Cell A1 which contains the month end date for eg MONTH ENDED 29/04/2022

I have public holidays in Cells O2:O13

I need to formula to extract the run date in cell C1 , which will be the first working day in the new month, being 3 may 2022, based on the month end date on A1

Eg

The Sale report for April will be run on the 3 May 2022

See link below

Formula to extract first workday after month end.xlsx

Sheet1 MONTH ENDED 29/04/2022,The Sale report for 29/04/2022 will be run on the 2,public Holidays should Be,The Sale report for April will be run on the 3 May 2022,1/1/2022 3/21/2022 4/15/2022 4/18/2022 4/27/2022 5/2/2022 6/16/2022 8/9/2022 9/24/2022 12/16/2022 12/25/2022 12/26/2022

docs.google.com

Your assistance is most appreciated

 

[Fluff]

Why should it be the 3rd & not the 2nd of May?

 

[bobsan42]

I personally do not like to rely on extracting numbers or dates from text
If you are the one setting up the file, make sure you have the dates you need stored in seprate cells as dates, then use them for calculations or to include them in texts.
If you want the next working day in C1, use this:

=WORKDAY(DATEVALUE(TRIM(RIGHT(A1,10))),1,$O$2:$O$13)

If you need to customize weekends you should use WORKDAY.INTL

 

[bobsan42]

Why should it be the 3rd & not the 2nd of May?

Probably because the 2nd is included in the holidays range.

 

[howard]

the 2 nd of May is a public holiday in South Africa (see public Holidays on Col O)

 

[bobsan42]

or this to get you all the way:

="The Sale report for " &TEXT(DATEVALUE(TRIM(RIGHT(A1,10))),"[$-en-ZA]mmmm;@")& " will be run on the " & TEXT(WORKDAY(DATEVALUE(TRIM(RIGHT(A1,10))),1,$O$2:$O$13),"[$-en-ZA]d mmmm yyyy;@")

 

[Fluff]

Very difficult to tell that as when I down the file some of those dates are text & some are dates, so I have no way of knowing what those dates really are. Also I have no idea what public Holidays South Africa have.

As long as the dates on col O are real dates, you can use

="The Sale report for "&TEXT(RIGHT(A1,10),"mmmm")&" will be run on the "&TEXT(WORKDAY(DATE(YEAR(RIGHT(A1,10)+0),MONTH(RIGHT(A1,10)+0)+1,0),1,O2:O14),"d mmm yyyy")

 

[howard]

Thanks for your valuable input bobsan42

Your formula works perfectly​