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

jasriel

New Member
Joined
Apr 7, 2016
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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
    Capture.PNG
    20.1 KB · Views: 23

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jasriel

New Member
Joined
Apr 7, 2016
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,412
Office Version
  1. 365
Platform
  1. Windows

jasriel

New Member
Joined
Apr 7, 2016
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
Yes they seem to do exactly what I need. Thank you.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. Thanks for the follow-up. :)
 

jasriel

New Member
Joined
Apr 7, 2016
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,412
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 7, 2016
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,412
Office Version
  1. 365
Platform
  1. Windows
I'm afraid I am not following that. Can you post it with XL2BB ?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,477
Office Version
  1. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,181,678
Messages
5,931,369
Members
436,788
Latest member
er19

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top