#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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")) Code:
=IF(EOMONTH($D$6,-1)+COLUMNS($J$4:J4)>EOMONTH($D$6,0),"",TEXT(EOMONTH($D$6,-1)+COLUMNS($J$4:J4),"DDD")) Its a bit more complex but far more robust - see my example on your control sheet |
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
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! |
#5
|
||||
|
||||
Thank you Purfleet. I did not know about the Center Across Selections, vertical and horizontal. It is a better alternative to merge.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 |