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]

@Dreadnaught, apply the following custom CF formula to B4:B46 ...

=AND(ISNUMBER(B4),OR(B4<DATE(YEAR($V$1+15),MONTH($V$1+15),1),B4>=DATE(YEAR($V$1+45),MONTH($V$1+45),1)))

Set Font Color and Fill to white.

 

[Dreadnaught]

Good evening Erik,

Thanks for such a quick reply to my submission but I have some questions on applying the formula.

Under which CF option path should it be applied, is it, CF=> New Rule=> Use a formula to determine which cells to format?

I take it the formula will need to be adjusted for each subsequent cell as follows:

=AND(ISNUMBER(B4),OR(B4=DATE(YEAR($V$1+45),MONTH($V$1+45),1)))
=AND(ISNUMBER(B5),OR(B5=DATE(YEAR($V$1+45),MONTH($V$1+45),1)))
=AND(ISNUMBER(B6),OR(B6=DATE(YEAR($V$1+45),MONTH($V$1+45),1)))

I have to read up on the ISNUMBER function but reading the formula I kind of get the gist where the value of B4 is being checked against that of V1 adjusted by 45 (is the 45 a row count?).

Thanks,

Sean

 

[inactiveuserps07]

Hello, Sean.

First, I notice that only part of my formula copied in. Here's what it should be:

[COLOR=#333333]=AND(ISNUMBER(B4), OR(B4<[/COLOR]<date(year($v$1+15),month($v$1+15),1),b4 style="color: rgb(51, 51, 51); font-size: 12px;">DATE(YEAR($V$1+15),MONTH($V$1+15),1), B4>=DATE(YEAR($V$1+45),MONTH($V$1+45),1)))</date(year($v$1+15),month($v$1+15),1),b4>

Here's a step-by-step:

1. Select the range B4:B46.

2. From the Home tab, click "Conditional Formatting">"New Rule">"Use a formula to determine which cells to format."

3. Enter my formula above exactly in the field below "Format values where this formula is true:"

4. Click the [Format...] button.

5. On the "Font" tab, set "Color:" to white.

6. On the "Fill tab, set "Background color:" to white.

7. Click "OK" ... "OK" to accept the formula.

The formula will automatically apply the same rule from B4 to every other cell in the selected range (so all the way to B46).

Let's make sure that is working for you. Then let me know here, and I'll explain how it works if you like.

 

[Dreadnaught]

Good afternoon Erik,

I see the missing part of the formula would account for why I was having problems yesterday in it working or understanding it .

Using the full formula I was able to get it to work but had to make 1 adjustment as the range B4:B46 has several rows of merged cells between then so I applied the formula as provided to B4:B10 and it worked.

I then amended the three B4 values to B40 and inserted that via the CF to B40:B46 and it also worked there, I then tested with a range of different dates and it worked fine but I had to make 1 more adjustment for if February started on a Saturday the month would finish in the 4th section. I amended the three B4 values to B31 and inserted that via the CF to B31:B37 and ran through all the extremes and it is working great.

It would be great if you broke it down. I am able to see the section where B4< and then B4>= the value of V1but can't figure out what the +15 and +45 means and how it is used.

Thanks,

Sean

 

[inactiveuserps07]

Sean, you could have applied the CF formula from B4:B46 despite the merged cells by manually adjusting the range in the CF. But it sounds like you made it work for your needs however you did.

In short, since the value in V1 will always be within 7 days of the end of a month, adding 15 determines what the next month will be (i.e., "somewhere" into the next month) and adding 45 will get us into the month after that every time. By then adjusting those months to the first day of each using the DATE formula with the day set to 1, we have a way to determine which days are not in the current month (i.e., those before the first of the month after V1 and those at or after the first of the month two away from V1).

 

[Dreadnaught]

Hi Erik,

I was able to manually adjust the formal as you indicated to cover the full range and item 1 is totally solved.

Thanks for the formula and the explanation, both are very much appreciated and as Monsieur Alfonse would say done "Swiftly and with Style".

Can you tell me if the 2 goal seems doable since it is kind of the opposite to have the value of 1 cell be dependent on the value in a range of other cells?

Sean

 

[inactiveuserps07]

Sean, try copying this formula into D39:

[FONT=arial][COLOR=#000000]=[/COLOR][COLOR=#000000]SUMPRODUCT[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]IF[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]MONTH[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]MAX[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]IF[/COLOR][COLOR=#000000]([/COLOR][COLOR=#F7981D]B40:B46 [/COLOR][COLOR=#000000]< [/COLOR][COLOR=#000000]DATE[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]YEAR[/COLOR][COLOR=#000000]([/COLOR][COLOR=#7E3794]$V$1[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#1155CC]45[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000]MONTH[/COLOR][COLOR=#000000]([/COLOR][COLOR=#7E3794]$V$1[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#1155CC]45[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]1[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#F7981D]B40:B46[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]0[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000]MONTH[/COLOR][COLOR=#000000]([/COLOR][COLOR=#7E3794]$V$1[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#1155CC]1[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000]MAX[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]IF[/COLOR][COLOR=#000000]([/COLOR][COLOR=#F7981D]B40:B46 [/COLOR][COLOR=#000000]< [/COLOR][COLOR=#000000]DATE[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]YEAR[/COLOR][COLOR=#000000]([/COLOR][COLOR=#7E3794]$V$1[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#1155CC]45[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000]MONTH[/COLOR][COLOR=#000000]([/COLOR][COLOR=#7E3794]$V$1[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#1155CC]45[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]1[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#F7981D]B40:B46[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]0[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=green]""[/COLOR][COLOR=#000000])[/COLOR][/FONT][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]

No additional CF required.

 

[inactiveuserps07]

Sean, this is shorter. Again, in D39 (no added CF required):

[FONT=arial][COLOR=#000000]=[/COLOR][COLOR=#000000]IFERROR[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]INDEX[/COLOR][COLOR=#000000]([/COLOR][COLOR=#F7981D]B40:B46[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000]MATCH[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]DATE[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]YEAR[/COLOR][COLOR=#000000]([/COLOR][COLOR=#7E3794]$V$1[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#1155CC]45[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000]MONTH[/COLOR][COLOR=#000000]([/COLOR][COLOR=#7E3794]$V$1[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#1155CC]45[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]1[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#1155CC]1[/COLOR][COLOR=#000000],[/COLOR][COLOR=#F7981D]B40:B46[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]0[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=green]""[/COLOR][COLOR=#000000])[/COLOR][/FONT]

 

[Dreadnaught]

Hi Erik,

The new formula worked great and I was able to tweak it (1 character) for the 4th week/segment and the time sheet is now totally finished and working great.

Many Kudos to you and thanks for all of your help,

Sean