Auto-filling a holiday calendar

[Bjarne_Iverson]

Hi,

I am trying to put together an automated system for filling in a holiday calendar for multiple people.
So far I have worked out how to calculate the working days and list all the days covered by the period but I am stuck transitioning this into a calendar format.
Any help or tips are greatly appreciated.

[mcmahobt]

At least to me, your request is a little confusing. Can you re-upload your example, but insert how you hope to have your final product look?

[Pete_UK]

Check out this thread from January:

https://www.excelforum.com/excel-gen...-calendar.html

Hope this helps.

Pete

[Bjarne_Iverson]

Thanks for the help @Pete_UK, I have used your linked work as a base. I was hoping to set the calendar out as an annual one.

@mcmahobt, I have attached a new copy of my form with an example calendar filled in.

[Pete_UK]

As you can have two or more people who could be taking holiday on the same day, I think you need to have several rows per month to accomplish this. Also, if you want to show an annual calendar, then perhaps it would be better to align all the days, as in a planning chart. My attachment (ef153...) in this thread:

https://www.excelforum.com/excel-gen...t-pattern.html

shows the type of thing that I have in mind, although this just has 3 rows per month and is for a fixed 3-shift pattern rather than holidays.

Hope this helps.

Pete

[Pete_UK]

I've set this up for you in your desired format in the attached file.

In the Booked sheet I have expanded the date ranges for booked holidays downwards rather than across. I changed your formula in H3 to this:

=IF(E3="","-",IF(F3<>"",(NETWORKDAYS(E3,F3,$A$4:$A$11))/IF(G3="y",2,1)))

which is copied down to the bottom of your (original) table, i.e. to row 252 - the hyphen helps to show where the formula is active.

I put zero in cell J2 and this formula in J3 (coloured orange):

=IF(OR(H3="",H3="-"),"-",ROUNDUP(H3,0)+J2)

and when this is copied down (also to row 252) it gives a cumulative total of the number of entries that are expected. Note that the 0.5 for the entry on row 13 is rounded up, as it is still a single entry in the calendar.

This formula in L2:

=IF(ROWS($1:1)>MAX(J:J),"-",MATCH(ROWS($1:1)-1,J:J)+1)

will return the row number where a date range occurs for as many times as it is required to be shown, so there are 8 of row 3, 5 of row 4, 10 of row 5, and so on. Obviously, this table can be very much larger than the original table, so this formula (and all the others coloured blue) need to copied down much further than the original table - I've copied them to row 500.

The name is retrieved using this formula in M2:

=IF(OR(L2="",L2="-"),"",INDEX(D:D,L2)&IF(INDEX(G:G,L2)="Y"," - "&CHAR(189),""))

Note here that I have tagged on to the name the symbol ½ to denote a half-day being booked - you can see this effect in cell M60.

This formula in N2 will retrieve the first date:

=IF(M2="","",IF(L2=L1,N1+1+IF(WEEKDAY(N1+1,2)>5,2,0),INDEX(E:E,L2)+COUNTIF(L$2:L2,L2)-1))

and in subsequent rows it will return the next sequential date if it is for the same record, but it will jump over any weekend dates that would occur in that sequence.

Finally, this formula in O2:

=IF(N2="","",N2&"_"&COUNTIF(N$2:N2,N2))

tags each date with a unique sequential number, to allow for multiple bookings on the same day.

In the Calendar sheet you could have this formula to return the first name in cell B2:

=IFERROR(INDEX(Booked!$M:$M,MATCH(DATEVALUE(B$1&$A2&2017)&"_1",Booked!$O:$O,0)),"")

but to return other names to that cell you will have to concatenate values together and separate them with a line feed character (code 10). Consequently, this is the formula that I've used in cell B2:

=IFERROR(INDEX(Booked!$M:$M,MATCH(DATEVALUE(B$1&$A2&2017)&"_1",Booked!$O:$O,0)),"")
&IFERROR(CHAR(10)&INDEX(Booked!$M:$M,MATCH(DATEVALUE(B$1&$A2&2017)&"_2",Booked!$O:$O,0)),"")
&IFERROR(CHAR(10)&INDEX(Booked!$M:$M,MATCH(DATEVALUE(B$1&$A2&2017)&"_3",Booked!$O:$O,0)),"")
&IFERROR(CHAR(10)&INDEX(Booked!$M:$M,MATCH(DATEVALUE(B$1&$A2&2017)&"_4",Booked!$O:$O,0)),"")
&IFERROR(CHAR(10)&INDEX(Booked!$M:$M,MATCH(DATEVALUE(B$1&$A2&2017)&"_5",Booked!$O:$O,0)),"")

This will give you up to 5 names on any one date, but I'm sure you can see how you can add more if required. The cell needs to be formatted to wrap-text, and the column width and row height may need to be adjusted to suit your real names. This formula can be copied across and down to fill your calendar, although from January onwards you will need to change 2017 to 2018 (5 times). For 30-day months, and for February, I've shaded out the cells that don't exist. You might like to apply conditional formatting to the calendar so that weekends are also shaded out.

So, all this will take place automatically, so you just need to put some real data in your original table and see it displayed as appropriate on the Calendar sheet.

Hope this helps.

Pete