Share via

Annual leave calendar for multiple people

Anonymous
2024-01-05T06:58:07+00:00

Good morning,

I’m trying to create an annual calendar to track up to 10 people’s vacation/absence

It would be easiest if we could enter the people in a workbook with the absences and then have it plotted in a calendar. It should be an annual calendar, not a monthly one for easy viewing, though 6-months could work too. Vertical layout probably best. I’ve looked at the templates available and none allow data entry; one only seems a possibility (Calendar any year horizontal), but because it is horizontal it prints out very small, and it’s a perpetual calendar so no days are indicated.

Thank you for any pointers,

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-01-05T12:50:14+00:00

    Yes, your idea works. Can we automate weekends (Sundays) and public holidays, I did this last year, manually, so it's a bit tedious to have to go edit out the sundays and public holidays

    1 person found this answer helpful.
    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-01-05T11:44:57+00:00

    I need a calendar that shows the year for everyone, to avoid overlapping absences 🤷‍♀️

    Hi Hilary,

    I can't guess what you're imagining. ;-) What about to insert one column for each employee?

    With just a few employees this is fairly manageable... with 10 employees this of course results in 12*(10+1) = 132 columns.

    A visual inspection or representation in the form is not really compact. Therefore, most people use a horizontal or vertical calendar that shows a cell for each day and each employee. However, you don't want that resulting in 370 rows or columns because it looks bad printed on a piece of paper...

    Make a draft yourself with manual colors and entries and show it to me. Then I can certainly help you automate this with formulas.

    Andreas.

    1 person found this answer helpful.
    0 comments
  3. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-01-05T09:14:59+00:00
    0 comments
  4. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-01-05T07:30:45+00:00

    A1: 2024

    B1: =DATE(A1,1,1)
    C1: =EDATE(B1,1)

    drag C1 to the right, format as "MMM"

    A2: 1
    A3: =A2+1

    drag down

    P2: =COUNTIF(B2:M32,"V")

    One sheet for one employee. Something like this?

    https://www.dropbox.com/s/bcptmmx24aw1mtq/9bd0e488-cbb2-459d-8f61-1e4b4bc78d01.xlsx?dl=1

    Andreas.

    0 comments