Empty cell if previous cell is the last day of the month

[jasriel]

Hi everyone,

I cannot seem to find the answer to my particular problem, hence my post. So, I am making an excel spreadsheet for work. It has dates 1 to 31 and corresponding days to the dates. So for instance this month 9/1/21 should be a Wednesday. So I would have WE (day of the week) in one cell and 1 (day of the month) in the other cell. Now that being said when days end like this month at 30, I need the last cell to be blank, but if the month does have 31 days I need it to fill that day in accordingly. Same thing would be for February, if it is a leap year or simply a regular year with 28 days, I would need 29 (pending leap year), 30 and 31 to be empty. I have uploaded a picture of the spreadsheet I am working on, so simply enter the date on sheet 1. Then it will change the corresponding information on sheet 2. So the Month and Year at the top right of the sheet will change and the MO, TU, WE, TH, FR, SA, SU will change accordingly also. I am not sure how to upload the actual spreadsheet, but if anyone would like me to send it to them send me a PM on here and I will send it. I am posting this at work so I am unable to install anything to the computers, so using a mini-sheet is out of the question.

Anyway I hope someone has an idea how to accomplish this as I do not.

Thank you

 

[jimrward]

ok the following formula assumes that the current month is in A1, and the formula can be in all of the cells or from column 28 onwards
it looks at the date in A1 and checks if by adding a day does the month change, if it changes then set the cell to empty, if no change in the month then increment the day by one, the first cell is set to 1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	01/10/2021
2
3	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
Sheet1

Cell Formulas
Range	Formula
B3:AE3	B3	=IF(A3="","",IF(MONTH($A$1)<>MONTH(DATE(YEAR($A$1),MONTH($A$1),DAY(A3)+1)),"",DAY(DATE(YEAR($A$1),MONTH($A$1),DAY(A3)+1))))

 

[bobsan42]

MrExcel Playbook 02 2021-09.xlsm
	AA	AB	AC	AD	AE	AF	AG	AH	AI
1								year	2021
2								month	9
3
4
5
6	Fri	Sat	Sun	Mon	Tue	Wed	Thu
7	24	25	26	27	28	29	30
Sheet5

Cell Formulas
Range	Formula
AA6:AH6	AA6	=AA7
AA7:AE7	AA7	=Z7+1
AF7:AH7	AF7	=IF(MONTH(AE7+1)=MONTH(AE7),AE7+1,"")

I usually do this by comparing the months of the two dates.
For this reason the numbers of the days and the weekday names is easiest to stay dates. I just use custom number format to show the date the way I want it.
Custom number formats:
to show the day number only: dd
to show the abbreviated weekday name in US English: [$-en-US]ddd

 

[Toadstool]

Hi Jasriel,

I've put the date in cell AF5. Row 7 is formatted as Custom d

Jasriel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
5																																2/22/2021
6	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA	SU
7	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
Sheet1

Cell Formulas
Range	Formula
A6:AF6	A6	=LEFT(UPPER(TEXT(A7,"ddd")),2)
A7	A7	=DATE(YEAR($AF$5),MONTH($AF$5),1)
B7:AF7	B7	=IF(MAX($A$7:A7)=EOMONTH($A$7,0),"",A7+1)

 

[Peter_SSs]

@jasriel
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jasriel]

Hi everyone,

Thank you for the replies. As soon as I am back at work I will give each of them a shot and see which works best for what I am trying to accomplish. Then I will post an update.

 

[Peter_SSs]

As soon as I am back at work I will give each of them a shot and see which works best for what I am trying to accomplish.

Unfortunately you didn't take up my suggestion about disclosing your Excel version.

IF you happen to have MS365 then perhaps this might be best as it only requires formulas in A6 and A7 (no copy across) and the cells over there on the right will take care of themselves.

21 09 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
6	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH
7	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
Days

Cell Formulas
Range	Formula
A6:AD6	A6	=LEFT(UPPER(TEXT(EOMONTH(TODAY(),-1)+A7#,"ddd")),2)
A7:AD7	A7	=SEQUENCE(,DAY(EOMONTH(TODAY(),0)))
Dynamic array formulas.

 

[jasriel]

My apologies, when I did my earlier reply I was short on time and did not have a chance. I had a few more minutes right now and I updated the version I am using. Ty for the reminder. I will try all the solutions that have been suggested and see which one works best once I get back to work tonight.

 

[Peter_SSs]

OK, no need to test mine then as it will not work with Excel 2019.

Thanks for updating your profile though.

 

[jasriel]

Hi again everyone, I am trying all the replies and they work for what is being accomplished but I realize I forgot to state one thing in my original posting. I only need the days of the week that go past the last day of the month to be blank, the days of the month will always show as 1-31.