Forum Discussion

AC_Barron's avatar
AC_Barron
Copper Contributor
Jun 07, 2023

Calendar for Scheduling Time Away

I'm looking for a simple Excel spreadsheet/calendar template that a group of ten people can use for the rest of the year and beyond to schedule our time away from the office. Days indicating the days off, where we will be, whether we will have phone, internet or any other type of access to occasional or extended communication.  We can set up three of four letter indications for each item of availability.  

User Adoption

2 Replies

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    Jun 12, 2023

    AC_Barron 

    I have attached an example. If you find you want a different style of calendar (year-at-a-glance, or weeks that start with Monday, etc.), the following technique can be adapted for that. This example assumes that your Excel is Excel 2019 or a more recent version; the main formulas use the function TEXTJOIN.


    The technique assumes that the absence data is stored in a specific range of cells or in an Excel table. I created a table named tblAbsences, on the EmpeeData worksheet. You would add/edit/remove rows in this table (they do not have to be in any particular order), and the automatic formula for the Calendar Code column derives the text that will be displayed on the Month View worksheet calendar.


    The Month View calendar derives its day numbers from the values you select/type in cells B2/C2. The formulas in all the cells below those day numbers (or where those day numbers could appear in other months) do the same work:

    1. Derive an Excel date value from cells B2, C2, and the day number cell just above it.
    2. Filter the entries in tblAbsences that apply to this derived date, grabbing just the Calendar Codes.
    3. Concatenate the filtered Calendar Codes, separating them with a delimiter (of one or more characters in cell D2; you can change these).

    Here is such a formula, specifically for cell B8:

    =LET( date, DATEVALUE(B7&"-"&MoMonth&"-"&MoYear),
    matches, FILTER( tblAbsences[Calendar Code], (tblAbsences[Start of Absence]<=date) * (tblAbsences[End of Absence]>=date), ""),
    result, TEXTJOIN($D$2, TRUE, matches),
    IFERROR(result,"") )


    I protected the Month View worksheet against accidental changes, so that only cells B2, C2, and D2 are normally editable. But if you want to edit this calendar for some reason (e.g., to increase row heights or change fonts), you can unprotect it by right-clicking the worksheet tab and selecting "Unprotect Sheet…".

     

Resources