+ Reply to Thread
Results 1 to 21 of 21

Add Holidays to Calendar

  1. #1
    Registered User
    Join Date
    06-23-2022
    Location
    city
    MS-Off Ver
    365
    Posts
    99

    Add Holidays to Calendar

    Hello,

    I have this calendar with events thanks to John Topley and Pete_UK.

    I did some modifications of the original and would also like to add Holidays on the calendar but still maintain the formulas that pull in the other events.

    Is this possible?

    Thank you very much for your help,
    KaylaP
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    There is a new function available to the insider beta channel in MS365 called VSTACK. It is due to be rolled out some time this year, I believe. I've taken advantage of it here to combine the activities and holidays into one block of data whose columns could be hidden:

    =SORT(VSTACK(Table13,Table1[[Date]:[Activity]]),1,1)

    The new formula then reads from this source data.

    The new MS365 formula starting in E7 is:

    =IFERROR(IF(E6="","",FILTER(Activities!$Q:$Q,Activities!$O:$O=E6)),"")

    You would need to clear ALL other formulae from the date body sections (your numbered rows for each date) before using this. It uses the date field, so there is no need to count activities per day.

    I hope this helps. There are other ways to combine the data (PowerQuery for one) if you don't have access to VSTACK - just shout if you need help with that.
    Attached Files Attached Files
    Last edited by AliGW; 06-24-2022 at 02:27 AM. Reason: Typo fixed.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    And here is a PQ version in case VSTACK is not possible for you. The queries are set to automatically refresh every minute, so any additions to events will appear in the calendar after 60 seconds maximum.
    Last edited by AliGW; 06-24-2022 at 02:19 AM. Reason: Additional info.

  4. #4
    Registered User
    Join Date
    06-23-2022
    Location
    city
    MS-Off Ver
    365
    Posts
    99

    Re: Add Holidays to Calendar

    Thank you AliGW I tried the VStack version and it works fantastic. This is the first time I heard about this way. The concept to combine the tables is brilliant by your part.

    I can't thank you enough and do not know how I could have done this on my own.

    My purpose is to eventually use this calendar for my school and work activities also which I will give some more thought.

    Have a wonderful day ahead!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    You, too, and I am so glad it worked for you. It's really worth updating formulae to use the newer dynamic array functions where you can as they are very slick.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    This version uses the SEQUENCE function to set the dates on the top row.
    Last edited by AliGW; 06-24-2022 at 05:13 AM. Reason: Workbook replaced with improved version.

  7. #7
    Registered User
    Join Date
    06-23-2022
    Location
    city
    MS-Off Ver
    365
    Posts
    99

    Re: Add Holidays to Calendar

    I appreciate your help very much. I was wondering if this could be another way. Since Pete has the formulas in the calendar already which gets the info from the first few columns of the Activities sheet could that area combine from the entered events and holidays? So this way it will need formulas to combine them in the Activities sheet.

    Please see attached where I highlighted the columns D and E in green that will need formulas.

    Maybe this way will help me to understand it better.

    Thank you
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    The reason I offered the solution I did was precisely to avoid this complication. Give me ONE good reason why you need to do it using Pete's formulae instead of taking the new approach.

  9. #9
    Registered User
    Join Date
    06-23-2022
    Location
    city
    MS-Off Ver
    365
    Posts
    99

    Re: Add Holidays to Calendar

    I ran into errors when trying to clear the calendar and some of the formulas were grayed out. Also when I was trying to reformat the formulas were not working.

    It is okay to stay with your versions if it is too much trouble.

    I will continue to learn.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    OK - in D4:

    =LET(e,FILTER(H4:I50,H4:H50<>""),h,FILTER(L4:M14,L4:L14<>""),SORT(VSTACK(e,h),1,1))

    Then you will need to update Pete's formula to work with it:

    =IF(ISNA(MATCH(E6&"_"&$D7,Activities!$B:$B,0)),"",INDEX(Activities!$E:$E,MATCH(E6&"_"&$D7,Activities!$B:$B,0)))

    ... some of the formulas were grayed out.
    You need to read up on #SPILL! arrays.
    Attached Files Attached Files
    Last edited by AliGW; 06-24-2022 at 06:08 AM. Reason: Workbook attached.

  11. #11
    Registered User
    Join Date
    06-23-2022
    Location
    city
    MS-Off Ver
    365
    Posts
    99

    Re: Add Holidays to Calendar

    Wow you rock! This is a perfect solution to my issues.

    I really am amazed on how you can derive this so quickly.

    I do hope also that others can benefit from the use of this version of the calendar activity workbook.

    Cheers!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    No worries.

  13. #13
    Registered User
    Join Date
    06-23-2022
    Location
    city
    MS-Off Ver
    365
    Posts
    99

    Re: Add Holidays to Calendar

    I must have done something wrong. I added rows under the holidays for events that occur on the same day every year such as birthdays and anniversaries. The formulas for Vstack are not working now. How are these entered? I tried to adjust the range in the formula but that does not do it.

    If you have the time can you take a look please?
    Attached Files Attached Files

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    You should NOT have opened another thread on this - I have closed the other thread and you should continue here.

    You will need to go with another option. I'll have a look shortly, but am a bit tied up at the moment.

    Please remove the SOLVED tag from this thread.

  15. #15
    Registered User
    Join Date
    06-23-2022
    Location
    city
    MS-Off Ver
    365
    Posts
    99

    Re: Add Holidays to Calendar

    Very sorry for opening another thread. I did not realize this was inappropriate.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    Step-by-step!

    Your repeat birthdays and anniversaries need entering like this as a formula:

    =DATE(YEAR(TODAY()),1,22)

    This will mean that they always have this year's year, but the month and day (in this case 22 January) will remain constant.

  17. #17
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Add Holidays to Calendar

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    Yes, Fluff - that works.
    Last edited by AliGW; 06-24-2022 at 08:50 AM. Reason: Workbook attached.

  19. #19
    Registered User
    Join Date
    06-23-2022
    Location
    city
    MS-Off Ver
    365
    Posts
    99

    Re: Add Holidays to Calendar

    This newest version is best I believe. It works just fine with my Microsoft 365 now.

    I wish to thank everyone for the fantastic help and support.


  20. #20
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Add Holidays to Calendar

    Glad to help & thanks for the feedback.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Add Holidays to Calendar

    Happy days!

+ 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. Delayed holidays in Calendar
    By Phaseshifter in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-15-2021, 02:40 PM
  2. [SOLVED] Highlighting countrywise Holidays in a calendar
    By anwitha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2021, 03:58 AM
  3. [SOLVED] Holidays in Cell Next to Day on Calendar
    By BobBrown60 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-02-2021, 08:37 AM
  4. [SOLVED] Reserve Calendar Days Row for Holidays Only
    By ynab in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-16-2020, 03:09 PM
  5. [SOLVED] VBA Calendar Help-Holidays
    By Ajhimm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-30-2017, 11:21 AM
  6. [SOLVED] Trying to add holidays to existing formula for calendar.
    By MrOchoa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2016, 06:46 AM
  7. Auto Populate Holidays in a Calendar
    By popeye000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-02-2016, 03:39 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