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

 

[Peter_SSs]

Does this do what you want then?

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	31
Days

Cell Formulas
Range	Formula
A6:AE6	A6	=IF(COLUMNS($A:A)>DAY(EOMONTH(TODAY(),0)),"",LEFT(UPPER(TEXT(EOMONTH(TODAY(),-1)+A7,"ddd")),2))

 

[jasriel]

From what I see being done it looks like it. I will give it a shot and let you know in a few minutes. Thank you for the fast reply

 

[Peter_SSs]

Does this do what you want then?

If so, this would be slightly simpler

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	31
Days

Cell Formulas
Range	Formula
A6:AE6	A6	=IF(A7>DAY(EOMONTH(TODAY(),0)),"",LEFT(UPPER(TEXT(EOMONTH(TODAY(),-1)+A7,"ddd")),2))

 

[jasriel]

Yes they seem to do exactly what I need. Thank you.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[jasriel]

Okay, I have run into a problem using the fore mentioned code. I have had time and been inputting everything and it worked out great if you are printing the pages out for the current month, however this is where the problem happens. Sometimes we print out the pages days before the new month happens. So for instance if it is September 25th and we are printing documents for 1 Oct 2021, when I change the date over to October, the weekdays do not change accordingly. Is there anyway to get days 2 through 31 to be based off of the 1st day where they change accordingly? If so I have been able to make the first day change off of whichever date I input, I just do not know how to get the days at the end of the month to work like I originally wanted based upon this new problem I have.

 

[Peter_SSs]

Is cell A7 a date formatted to just show the day, or is it just the number 1?

Do B7 through to AE7 contain formulas? If so, what is the formula?

I have been able to make the first day change off of whichever date I input,

This might be answered by your response to the first question above, but where are you inputting a date? And is the date you input always the first day of a month?

 

[jasriel]

Question one: I have the date to be entered on a separate tab, to minimize what people need to focus on. Then in a random cell, in my case it is cell MB100, I have the following formula which points to the date on the previous tab... =Control!D6

Then in the cell MC100 I have this formula to format the date to the 2 character day of the week... =LEFT(UPPER(TEXT(MB100,"ddd")), 2)

Then I have where the number one is pointing to MC100 using =MC100

Question 2: Yes the date entered will always be the 1st day of whichever month we need the forms for.

There might be an easier way to do all of this, but honestly I have no idea how to simplify it, I am more of an MS Access user, trying to muddle my way through excel because for what I was wanting to do Access was not letting me do the formatting the way I wanted due to limits.

 

[Peter_SSs]

I'm afraid I am not following that. Can you post it with XL2BB ?

 

[NoSparks]

If a VBA solution would be acceptable or of any interest...

right click on the "Control" sheet tab,​

click 'View Code' ,​

paste this Event macro into the sheet module window that opens,​

save the file as an .xlsm (Excel Macro-Enabled Workbook),​

Done.​

Now any change to the date in D6 of "Control" sheet will automatically populate row 6 of sheet "PRS 2,3,4"
The month and year entered into D6 are used and the day used will always be 1 no matter the day entered.
The formulas in those 287 merged cells that make up the 31 days of row 6 are over-written,
MB100:MC106 no longer needed.

Tested on a copy of the file you provided at msofficeforums

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim startofmonth As Date, endofmonth As Date, printdate As Date
    Dim i As Long, j As Long, wkDay
    
' restrict to specific cell
If Target.CountLarge > 1 Or Target.Address <> "$D$6" Then Exit Sub

Application.ScreenUpdating = False

' clear contents of row 6 A:KA
    With Sheets("PRS 2,3,4")
        For i = 8 To 310 Step 9
            .Cells(6, i).MergeArea.ClearContents
        Next i
' establish start and end of month
    startofmonth = DateSerial(Year(Target), Month(Target), 1)
    endofmonth = DateSerial(Year(startofmonth), Month(startofmonth) + 1, 0)
' starting point for variables
    i = 0:  j = 8
' loop merged areas inserting two letter weekday
        Do
            printdate = startofmonth + i
            wkDay = Weekday(printdate)
            wkDay = UCase(Left(Format(wkDay, "ddd"), 2))
            .Cells(6, j).MergeArea = wkDay
            i = i + 1
            j = j + 9
        Loop Until printdate = endofmonth
    End With
    
Application.ScreenUpdating = True

End Sub