Monthly Timesheet which only displays values for the current month and blanks out values for previous or next month

[Dreadnaught]

Good day all,

The time sheet currently used was created in MS Word and updating it every month requires updating quite a few cells (Individual Days, End of Week, Current Month, etc.) so to save time I translated the time sheet into Excel keeping an almost identical layout.

The existing MS Word sheet is designed to cover 5 weeks each running from Monday to Sunday but the month runs from the actual start date to end date of the 'current month'. So November 2017 will run from the Wednesday in the 1st weeks section (Monday and Tuesday will be blank) to the Thursday in the 5th weeks section (Friday to Sunday will be blank).

It also contains a Week Ending sub section for each of the 5 sections. Ideally the aim is to have the information displayed on the spreadsheet automatically adjust based on the 'current month' entered and only display the dates for this month and blank cells any period before or after.

The 2 areas on which I am looking for assistance

1) I have the dates being automatically filled in starting in B4 based on a start date of sorts either C63 or V1 (Cells Highlighted in Yellow) but what I want is if the day is not within the current month as per C63/V1 the text is the cell is formatted to White and thus appears to be blank (cells in Orange).

2) The Week Ending date in the 5th section (Cell in Olive) should display the last date in the current month if it falls into this section otherwise (if the 1st February is a Monday thus the 28th falls into the 4th section, Non Leap Year) be blank by formatting the test to White

I have been trying out Conditional Formatting but have not been able to solve my query.

Is what I am aiming for possible?


OS Windows 7 to 10
Users with a variety of version of Office/Excel from 2003 to 2013

For ease of reference I have attached the MS Word and Excel versions of the time sheet:

MS Word
https://drive.google.com/file/d/12KX-bAJIo7IRQ7v70vmWURajmNZEAMeX/view?usp=sharing

Excel
https://drive.google.com/file/d/189Az40hl6KkMMS8DT0wB_W_w0mk3EZSg/view?usp=sharing

Thanks in advance for any assistance

 

[inactiveuserps07]

Glad to hear it, Sean. I'm not sure that using white-out conditional formatting is your best approach; and I see some other potential pitfalls down the road. But if it's working for you fine as it is, I guess you can cross other bridges when you get to them.