Holiday Calendar

krisso

Active Member
Joined
Sep 16, 2005
Messages
291
I am trying to create a holiday calendar for my office that when the holiday is booked in the table (list) it highlights the days taken in the below calanders. I was pretty sure I could do this by index match until that didnt work that is, so now I am stuck. Has anyone got an idea of how I can achieve this.

Thanks

Date How many Days Whos Holiday Book Until
19-Aug 5 Chris 23-Aug
19-Mar 1 Grant 19-Mar
28-Mar 0.5 Grant 28-Mar
04-Apr 1 Chris 04-Apr
26-Apr 0.5 Grant 26-Apr
22-Jul 5 Grant 26-Jul
29-Jul 1 Chris 29-Jul
07-May 0.5 Grant 07-May
30-May 0.5 Grant 30-May
10-Jun 0.5 Grant 10-Jun
14-Jun 0.5 Grant 14-Jun
04-Jul 1 Chris 04-Jul

April
Name 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Chris
Grant

May
Name 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Chris
Grant

June
Name 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Chris
Grant

July
Name 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Chris
Grant
 
Thanks Mumps and AlanY

https://docs.google.com/spreadsheets/d/1iBilVOblGWctuEwBTGrnKXvZrnxGZuiFUD3ayRSf-rE/edit?usp=sharing

Hopefully this works

Basically there are 2 tabs, one where the data is entered and the second where I want to populate the calendar against the relevant staff member

Thanks

i've adopted the formula in March on post#7 to your file

https://drive.google.com/file/d/1k_ezOuGdIqNn0gR7ps3NKv26NmVyT_Q2/view?usp=sharing

when you copy the formula to new month you need to adjust the row reference of that month as I done for June (from March)
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
@AlanY
Can you please post yur solution to the board, so that everyone can see it without having to download files, as per Rule#4.
Thanks
 
Upvote 0
@AlanY
Can you please post yur solution to the board, so that everyone can see it without having to download files, as per Rule#4.
Thanks

yes, sir.


Book1
ABCDE
1
2Holidays Remaining
3DateHow many DaysWhos HolidayBook UntilReturn to Work
419-Aug5Chris23-Aug27/8/2019
519-Mar1Grant19-Mar20/3/2019
628-Mar0.5Grant28-Mar28/3/2019
704-Apr1Chris04-Apr5/4/2019
826-Apr0.5Grant26-Apr26/4/2019
922-Jul5Grant26-Jul29/7/2019
1029-Jul1Chris29-Jul30/7/2019
1107-May0.5Grant07-May7/5/2019
1230-May0.5Grant30-May30/5/2019
1310-Jun0.5Grant10-Jun10/6/2019
1414-Jun0.5Grant14-Jun14/6/2019
1504-Jul1Chris04-Jul5/7/2019
Holiday 2019-2020



Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1March
2Name01020304050607080910111213141516171819202122232425262728293031
3Chris 
4GrantX
5
6April
7Name010203040506070809101112131415161718192021222324252627282930
8Chris
9Grant
10
11May
12Name01020304050607080910111213141516171819202122232425262728293031
13Chris
14Grant
15
16June
17Name010203040506070809101112131415161718192021222324252627282930
18Chris 
19GrantX
Calendar 2019-2020
Cell Formulas
RangeFormula
B3=IF(SUMPRODUCT(--($A3=Table_2[Return to Work]),--(B$2>=Table_2[Whos Holiday]),--(B$2<=Table_2[Date]))>0,"X","")
B18=IF(SUMPRODUCT(--($A18=Table_2[Return to Work]),--(B$17>=Table_2[Whos Holiday]),--(B$17<=Table_2[Date]))>0,"X","")
 
Upvote 0
Many thanks.

If I use that formula in B3 & drag it across, it changes the table columns to
=IF(SUMPRODUCT(--($A3=Table_2[Date]),--(C$2>=Table_2[Book Until]),--(C$2<=Table_2[How many Days]))>0,"X","")
then
=IF(SUMPRODUCT(--($A3=Table_2[How many Days]),--(D$2>=Table_2[Return to Work]),--(D$2<=Table_2[Whos Holiday]))>0,"X","")

How do you stop that?
 
Upvote 0
Many thanks.

If I use that formula in B3 & drag it across, it changes the table columns to
=IF(SUMPRODUCT(--($A3=Table_2[Date]),--(C$2>=Table_2[Book Until]),--(C$2<=Table_2[How many Days]))>0,"X","")
then
=IF(SUMPRODUCT(--($A3=Table_2[How many Days]),--(D$2>=Table_2[Return to Work]),--(D$2<=Table_2[Whos Holiday]))>0,"X","")

How do you stop that?

yes, that wasn't right. thanks.
not quite sure how to absolute reference a column in table, but this should work


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1March
2Name010203040506070809101112131415161718192021222324252627
3Chris 
4GrantX
5
6April
7Name010203040506070809101112131415161718192021222324252627
8Chris
9Grant
10
11May
12Name010203040506070809101112131415161718192021222324252627
13Chris
14Grant
15
16June
17Name010203040506070809101112131415161718192021222324252627
18Chris 
19GrantXX
20
21July
22Name010203040506070809101112131415161718192021222324252627
23Chris
24Grant
25
26August
27Name010203040506070809101112131415161718192021222324252627
28Chris
29Grant
30
Calendar 2019-2020
Cell Formulas
RangeFormula
B3=IF(SUMPRODUCT(--($A3='Holiday 2019-2020'!$C$1:$C$15),--(B$2>='Holiday 2019-2020'!$A$1:$A$15),--(B$2<='Holiday 2019-2020'!$D$1:$D$15))>0,"X","")
B18=IF(SUMPRODUCT(--($A18='Holiday 2019-2020'!$C$1:$C$15),--(B$17>='Holiday 2019-2020'!$A$1:$A$15),--(B$17<='Holiday 2019-2020'!$D$1:$D$15))>0,"X","")
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top