Monthly Timesheet which only displays values for the current month and blanks out values for previous or next month
Excel VBA Tools from Andrew
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    New Member Dreadnaught's Avatar
    Join Date
    Dec 2017
    Location
    Trinidad & Tobago, West Indies
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

     
    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...ew?usp=sharing

    Excel
    https://drive.google.com/file/d/189A...ew?usp=sharing

    Thanks in advance for any assistance

  2. #2
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

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

    Code:
    =AND(ISNUMBER(B4),OR(B4=DATE(YEAR($V$1+45),MONTH($V$1+45),1)))
    Set Font Color and Fill to white.

  3. #3
    New Member Dreadnaught's Avatar
    Join Date
    Dec 2017
    Location
    Trinidad & Tobago, West Indies
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

    Hello, Sean.

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

    Code:
    =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)))
    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.

  5. #5
    New Member Dreadnaught's Avatar
    Join Date
    Dec 2017
    Location
    Trinidad & Tobago, West Indies
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #6
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

    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).

  7. #7
    New Member Dreadnaught's Avatar
    Join Date
    Dec 2017
    Location
    Trinidad & Tobago, West Indies
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  8. #8
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

    Sean, try copying this formula into D39:

    Code:
    =SUMPRODUCT(IF(MONTH(MAX(IF(B40:B46 < DATE(YEAR($V$1+45),MONTH($V$1+45),1),B40:B46,0)))=MONTH($V$1)+1,MAX(IF(B40:B46 < DATE(YEAR($V$1+45),MONTH($V$1+45),1),B40:B46,0)),""))
    No additional CF required.
    Last edited by ErikTyler; Dec 7th, 2017 at 06:24 PM.

  9. #9
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    733
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

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

    Code:
    =IFERROR(INDEX(B40:B46,MATCH(DATE(YEAR($V$1+45),MONTH($V$1+45),1)-1,B40:B46,0)),"")
    Last edited by ErikTyler; Dec 7th, 2017 at 06:36 PM.

  10. #10
    New Member Dreadnaught's Avatar
    Join Date
    Dec 2017
    Location
    Trinidad & Tobago, West Indies
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

      
    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com