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

#### jasriel

##### New Member
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

#### Attachments

• Capture.PNG
20.1 KB · Views: 28
Does this do what you want then?

21 09 19.xlsm
6WETHFRSASUMOTUWETHFRSASUMOTUWETHFRSASUMOTUWETHFRSASUMOTUWETH
712345678910111213141516171819202122232425262728293031
Days
Cell Formulas
RangeFormula
A6:AE6A6=IF(COLUMNS(\$A:A)>DAY(EOMONTH(TODAY(),0)),"",LEFT(UPPER(TEXT(EOMONTH(TODAY(),-1)+A7,"ddd")),2))

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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

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

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

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.

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?

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.

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

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

VBA Code:
``````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``````

Replies
2
Views
89
Replies
4
Views
414
Replies
4
Views
188
Replies
2
Views
141
Replies
1
Views
99

1,218,575
Messages
6,143,311
Members
450,477
Latest member
teresab543

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back