How to run sequential functions in a single cell in excel

[AusFastLife]

Dear All,

This isn't actually a question. Just a post to explain how to achieve an effect that I was looking for, but couldn't find an answer to. But then when I was writing out my question to ask for advice on this forum, I actually worked it out. So I thought I would post it any way incase anyone else is looking for this solution.

To explain I wanted to run multiple functions sequentially one after the other in a single cell. but I couldn't work out how to do it.

I have a spread sheet which lists meeting dates. I wanted to create a formula in one cell that states the ordinal day of the week a given date falls on for that month.

For example the date (in format dd/mm/yyyy) 23/01/2023 is the 4th Monday of that month. The date 13/12/2023 is the 2nd Wednesday of that Month.

I had the formula "=ROUNDUP(DAY(D16)/7,0)" [formula 1] that gives me the week of that particular month. that a given date falls on eg if the date 23/1/2023 is in cell D16 then this formula returns the value "4" since this date is in the 4th week of January.

The formula "=I16&IF(AND(MOD(ABS(I16),100)>10,MOD(ABS(I16),100)<14),"th",CHOOSE(MOD(ABS(I16),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))" [formula 2] takes a number in a cell (in this example I have entered the number 1 in cell I16 and the formula returns the number "1st". If you change the number in cell I16 to 3 the formula returns the value "3rd" and so on.
Lastly I can pull the day of the week that the date falls on by referring to Cell E16

Please see the Mini-sheet below for the above described examples.

Reports and Compliance Dates.xlsx
	B	C	D	E	F	G	H	I
14	Event	Month	Meeting Date	Meeting Day	Actual Date Held	Comments
15
16	Ordinary Board Meeting	January	23/01/2023	Monday		4th Monday	1st	1
17	Ordinary Board Meeting	March	20/03/2023	Monday		3rd Monday
18	Extra-Ordinary Board Meeting - to discuss finance	April	17/04/2023	Monday		3rd Monday
19	Ordinary Board Meeting	May	15/05/2023	Monday		3rd Monday
20	Ordinary Board Meeting	July	17/07/2023	Monday		3rd Monday
21	Extra-Ordinary Board Meeting - to discuss finance	August	21/08/2023	Monday		3rd Monday
2) Future Years

Cell Formulas
Range	Formula
C16:C21	C16	=D16
D16:D21	D16	=LET(Meet,'1) Theoretical'!D16,Yr,$C$11,n,INT((DAY(Meet)+6)/7),FOM,DATE(Yr,MONTH(Meet),1),Guess,FOM-WEEKDAY(FOM)+WEEKDAY(Meet)+n*7,nPrime,INT((Guess-FOM+7)/7),Guess+7*(n-nPrime))
E16:E21	E16	=TEXT(D16,"DDDD")
H16	H16	=I16&IF(AND(MOD(ABS(I16),100)>10,MOD(ABS(I16),100)<14),"th",CHOOSE(MOD(ABS(I16),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
G16:G21	G16	=(ROUNDUP(DAY(D16)/7,0))&IF(AND(MOD(ABS(ROUNDUP(DAY(D16)/7,0)),100)>10,MOD(ABS(ROUNDUP(DAY(D16)/7,0)),100)<14),"th",CHOOSE(MOD(ABS(ROUNDUP(DAY(D16)/7,0)),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&" "&E16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D49,D52,D16:D23,D26:D29,D32,D37:D46	Expression	=WEEKDAY(D16,2)>5	text	NO

So in cell G16 I want to list which day of the week, in this case which Monday the date falls on. I ended up nesting [Formula 1] into the cell reference calls in [Formula 2] and then called on the day of the week in format dddd in cell E16 to be added on the end using the combined formula:

=(ROUNDUP(DAY(D16)/7,0))&IF(AND(MOD(ABS(ROUNDUP(DAY(D16)/7,0)),100)>10,MOD(ABS(ROUNDUP(DAY(D16)/7,0)),100)<14),"th",CHOOSE(MOD(ABS(ROUNDUP(DAY(D16)/7,0)),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&" "&E16

It works like a charm.

I hope it helps others who might also need this.

Best regards,
Haydn