+ Reply to Thread
Results 1 to 9 of 9

Delayed holidays in Calendar

  1. #1
    Registered User
    Join Date
    10-08-2021
    Location
    Canada
    MS-Off Ver
    356
    Posts
    13

    Post Delayed holidays in Calendar

    Hello,

    Please keep in mind I've only recently started using these functions, as well as macros. So I'm a newb.

    I'm creating a dynamic vacation calendar. I've got the week ends on conditional formatting, and they automatically adjust to match the year when I change it.
    My problem is that I can't do the same thing with holidays, because if a workday Holiday falls on a week end, our actual day off is the following Monday, or the previous Friday. (Except things like good Friday that are not based on dates)

    I was just going to forget about holidays, but then I thought I could just create a formula that has each date check if the previous or following day is both a holiday and a week end day, and then change color if both conditions come up true. But I have no idea how to do that. (I'm also trying to figure out how to set conditional formatting for things like good Friday, I KNOW it should be simple. I've been looking at that sheet too long.)
    Attached Files Attached Files
    Last edited by Phaseshifter; 10-12-2021 at 04:14 PM.

  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,369

    Re: Delayed holidays in Calendar

    Welcome to the forum.

    In the workbook you have shared, there are no holidays (that I can see). There will need to be a list somewhere, otherwise Excel has no hope whatsoever of adding them in any way, shape or form to your calendar.

    Perhaps you could add a few pertinent ones and then someone can advise.
    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 Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Delayed holidays in Calendar

    I took the liberty of changing date calculations and adding drop downs for years/calendar months

    in E11/E12

    =DATE(D2,MONTH(D11 &0),1)

    Also added HOLIDAYS named range

    in CF

    =Match(E$12,holidays,0)

    Fill: Orange
    Attached Files Attached Files
    Last edited by JohnTopley; 10-09-2021 at 08:01 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Registered User
    Join Date
    10-08-2021
    Location
    Canada
    MS-Off Ver
    356
    Posts
    13

    Re: Delayed holidays in Calendar

    Quote Originally Posted by AliGW View Post
    Welcome to the forum.

    In the workbook you have shared, there are no holidays (that I can see). There will need to be a list somewhere, otherwise Excel has no hope whatsoever of adding them in any way, shape or form to your calendar.

    Perhaps you could add a few pertinent ones and then someone can advise.
    Right, I hadn't put them in originally when I didn't think it was possible.

    Quote Originally Posted by JohnTopley View Post
    I took the liberty of changing date calculations and adding drop downs for years/calendar months

    in E11/E12

    =DATE(D2,MONTH(D11 &0),1)

    Also added HOLIDAYS named range

    in CF

    =Match(E$12,holidays,0)

    Fill: Orange
    I originally had a dropdown for the year. I had to remove it because employees will have to put down their vacation on the calendar for each month, and Excel cannot lock those changes. Anything added to a cell in October for example will show up in every month. So unfortunately, the dropdown for months can't work for me

    Oh, also, it looks like if I use any year but 2021 on your sheet, the holidays disappear.

    But can you tell me why you changed the date calculations? What was wrong with the original format?
    (Oh, and how does the match function work?)
    Last edited by Phaseshifter; 10-12-2021 at 08:41 AM.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Delayed holidays in Calendar

    The holidays will disappear simple because they relate to 2021 !!

    The date change was in part due to using the month drop down but as that is redundant apparently you can revert to your format. But i suggest you use a DV for the year rather than a form control.

    As I understand your process, there will a monthly calendar per employee?

  6. #6
    Registered User
    Join Date
    10-08-2021
    Location
    Canada
    MS-Off Ver
    356
    Posts
    13

    Re: Delayed holidays in Calendar

    What does DV stand for?

    I used a dropdown because otherwise, I'm afraid whoever needs to update the calendar might miss it and screw up my sheet. (Though I have another version where you can type whichever year you want). The downside is that I can't change the font in the control list to make it larger an easier to see

    But the vacation tracker calendar is to be shared by all employees. So they'll all be putting their leave on the same document.

    (Updated the document)

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Delayed holidays in Calendar

    DV = Data Validation

  8. #8
    Registered User
    Join Date
    10-08-2021
    Location
    Canada
    MS-Off Ver
    356
    Posts
    13

    Re: Delayed holidays in Calendar

    I can't figure out how to make it work. I can't have the formula check the two previous days, since the first day of the month would not have anything preceding it. And Easter is a problem.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Delayed holidays in Calendar

    As there nothing to link one month to the next there is no way to manage this problem with formulae.

    You require something along the lines of the attached which takes a annual rather than monthly view.

    "Activities" can be your holidays and gives you the possibility of checking the before/after days for holidays

    (Originator is Pete_UK)
    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. [SOLVED] Highlighting countrywise Holidays in a calendar
    By anwitha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2021, 03:58 AM
  2. [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
  3. [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
  4. [SOLVED] VBA Calendar Help-Holidays
    By Ajhimm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-30-2017, 11:21 AM
  5. [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
  6. [SOLVED] Account for holidays in an automated calendar
    By Lizzietish11 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-24-2014, 10:13 AM
  7. Excel to Outlook as Delayed Email (was Task) (was Calendar)
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-30-2008, 11:16 AM

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