+ Reply to Thread
Results 1 to 5 of 5

Adjusting Size of Calendar

  1. #1
    Forum Contributor
    Join Date
    04-23-2014
    Location
    United Kingdom
    MS-Off Ver
    Microsoft 365
    Posts
    149

    Adjusting Size of Calendar

    I have a calendar that uses just one formula. It works well, except I want to adapt its height according to the number of days shown.

    For example, at present, the calendar will consistently be six rows deep. For those months that don't have any days in the final row, the sixth row should not return. I still want the days that fall in the next month to show, but only if they are on a line containing days for the current month.

    As you can see, for December 2021, the sixth row is redundant because those are all for January 2022.

    Screenshot 2021-11-25 at 10.57.20.png

    Another example is if February has 28 days and the first of that month is Monday, only four rows should show.

    Screenshot 2021-11-25 at 10.55.16.png
    Attached Files Attached Files
    Last edited by Statto; 11-25-2021 at 06:58 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Adjusting Size of Calendar

    You could use conditional formatting in the bottom two rows to compare the month in $H$8 (or $H$5) with the month in B9 (and B10), and blank out the cells if they are not the same. You will need to ensure that these rules are first in the list of rules, and tick Stop if True.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    04-23-2014
    Location
    United Kingdom
    MS-Off Ver
    Microsoft 365
    Posts
    149

    Re: Adjusting Size of Calendar

    Ideally, I'd like to find a formulaic solution — a modification to the existing formula.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Adjusting Size of Calendar

    Please try

    =SEQUENCE(ROUNDUP((DAY(EOMONTH(F2&B2,0))+WEEKDAY(F2&B2,3))/7,0),7,(F2&B2)-WEEKDAY(F2&B2,3),1)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    04-23-2014
    Location
    United Kingdom
    MS-Off Ver
    Microsoft 365
    Posts
    149

    Re: Adjusting Size of Calendar

    Quote Originally Posted by Bo_Ry View Post
    Please try
    Attachment 757239
    =SEQUENCE(ROUNDUP((DAY(EOMONTH(F2&B2,0))+WEEKDAY(F2&B2,3))/7,0),7,(F2&B2)-WEEKDAY(F2&B2,3),1)
    Thanks, that worked great. However, I have updated the calendar so a start day can be selected. This has caused issues with some of the months.

    For example, February, March and November 2021 all contain the last week of the previous month when the start day is Tuesday.

    Screenshot 2021-11-29 at 00.32.23.png

    How can this be fixed?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 08-07-2017, 02:49 PM
  2. Auto Adjusting Table Size?
    By robfanucchi in forum Excel General
    Replies: 3
    Last Post: 07-05-2017, 02:05 AM
  3. Iterations, Self adjusting table size
    By 1000474903 in forum Excel General
    Replies: 0
    Last Post: 07-11-2014, 06:53 AM
  4. adjusting Column size of textbox
    By ali84pk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2011, 11:12 AM
  5. Adjusting Size of Rows and Columns
    By Aumkar in forum Excel General
    Replies: 2
    Last Post: 02-15-2010, 01:57 AM
  6. adjusting size of list box
    By sick stigma in forum Excel General
    Replies: 0
    Last Post: 06-22-2007, 08:12 AM
  7. [SOLVED] adjusting cell size in spreadsheets
    By [email protected] in forum Excel General
    Replies: 5
    Last Post: 12-21-2005, 02:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1