Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-20-2021, 05:02 AM
Jazsriel Jazsriel is offline Empty cell if previous cell is the last day of the month Windows 10 Empty cell if previous cell is the last day of the month Office 2013
Novice
Empty cell if previous cell is the last day of the month
 
Join Date: Sep 2021
Posts: 2
Jazsriel is on a distinguished road
Default Empty cell if previous cell is the last day of the month

Hello everyone, first time posting on this forum.




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. Also the monthly days 1-31 never change or are blank, they always show 1-31, the only ones I need to go blank if they are past the last day of the month are the weekdays Mo, Tu, We, Th, Fr, Sa, Su.


Anyway, the spreadsheet I have attached has 2 sheets. On the first called Control, you simply change the date in the appropriate block, which will always be the first of a month. Then, on sheet 2 you can see where the Month and Year at the top right of the page changed to whichever date you entered. Also you will not all the We, TH, Fr... have changed also. So, this is where the problem comes in, I need to for instance Sept to have the weekday be blanked out automatically (31st) based upon it not existing in September, however the 31 under it to still be there.


Anyway I know this is probably confusing even though in my mind it makes sense, if anyone has any ideas I would appreciate them.


Thank you.
Attached Files
File Type: xlsx PRS.xlsx (42.5 KB, 5 views)
Reply With Quote
  #2  
Old 09-20-2021, 07:37 AM
Purfleet Purfleet is offline Empty cell if previous cell is the last day of the month Windows 10 Empty cell if previous cell is the last day of the month Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 335
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Personally i would get rid of the Merged cells, nothing good will ever come from merged cells - they are a terrible idea.

Anyway rant over, in the first box (a1) i would put
Code:
=IF(EOMONTH($D$6,-1)+COLUMNS($J$4:J4)>EOMONTH($D$6,0),"",TEXT(EOMONTH($D$6,-1)+COLUMNS($J$4:J4),"DD"))
and in the one above

Code:
=IF(EOMONTH($D$6,-1)+COLUMNS($J$4:J4)>EOMONTH($D$6,0),"",TEXT(EOMONTH($D$6,-1)+COLUMNS($J$4:J4),"DDD"))
and incredment each number accross (which is horrible with merged cells - the Columns part deals with this but wont work with merged cells)

Its a bit more complex but far more robust - see my example on your control sheet
Attached Files
File Type: xlsx Copy of PRS_AR12.xlsx (43.7 KB, 10 views)
Reply With Quote
  #3  
Old 09-20-2021, 08:14 AM
Jazsriel Jazsriel is offline Empty cell if previous cell is the last day of the month Windows 10 Empty cell if previous cell is the last day of the month Office 2013
Novice
Empty cell if previous cell is the last day of the month
 
Join Date: Sep 2021
Posts: 2
Jazsriel is on a distinguished road
Default

I like what you did on the spreadsheet you posted back. I also partially agree with you on merged cells. However, I don't mind using them in a limited capacity. What I did on this spreadsheet, was sadly a necessary evil. I am recreating a government form and it was the only way I could get the spacing as close as I possibly could. I am going to take and run with what you did here. It works for what I need to do. Thank you.
Reply With Quote
  #4  
Old 09-20-2021, 09:19 AM
Purfleet Purfleet is offline Empty cell if previous cell is the last day of the month Windows 10 Empty cell if previous cell is the last day of the month Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 335
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

your call mate, but it will cause problems.

have a look at Centre Across Selection if you get time - it looks the same but is far less annoying!
Reply With Quote
  #5  
Old 09-21-2021, 03:09 AM
Marcia's Avatar
Marcia Marcia is online now Empty cell if previous cell is the last day of the month Windows 10 Empty cell if previous cell is the last day of the month Office 2019
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 429
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Purfleet. I did not know about the Center Across Selections, vertical and horizontal. It is a better alternative to merge.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Empty cell if previous cell is the last day of the month Empty date cell shows wrong month & year Learner7 Excel 3 06-16-2019 11:09 AM
Find last non empty cell (moving horizontally), then move one cell to the right klutch Word VBA 9 07-09-2018 06:16 PM
if cell contains date, then month, else blank Merlot Excel 4 10-03-2016 03:27 PM
Data validation,force cell to be filed with number if respective cell is not empty nicholes Excel Programming 0 08-01-2015 09:08 AM
Empty cell if previous cell is the last day of the month If id cell range is empty then should not allow to fill any other cell ubns Excel Programming 2 04-12-2015 06:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:32 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft