Holiday Calendar

[krisso]

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

 

[dmt32]

Hi,
rather than re-invent this there are plenty out there you can download for free.

Here is one example:https://trumpexcel.com/excel-leave-tracker/

but you can search for others

Dave

 

[krisso]

Hi Dave,

Thanks for the reply. Our holiday entitlement spans 2 years so the free ones that I have looked at I can't adjust which is why I have continued on with my own one.

Chris

 

[AlanY]

if could be easier if you can share the one you're working on, or just a small sample of it

 

[krisso]

Hi Alan,

I am not sure how to post a sample of it on here, other than what I have posted above

Thanks
Chris

 

[mumps]

This Forum doesn't allow posting of attachments. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets.

 

[AlanY]

have a look if this works for you

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

 

[krisso]

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

 

[Fluff]

If you just want to highlight the cell, select B3:AF4 and use this formula
=SUMPRODUCT(('Holiday 2019-2020'!$C$4:$C$15=$A3)*('Holiday 2019-2020'!$A$4:$A$15<=B$2)*('Holiday 2019-2020'!$D$4:$D$15>=B$2))

 

[mumps]

Try:

Sub UpdateCalendar()
    Application.ScreenUpdating = False
    Dim bottomAH As Long, rDate As Range, rDay As Range, i As Long, rng As Range, srchRng As Range, num As Long
    bottomAH = Range("AH" & Rows.Count).End(xlUp).Row
    For Each rDate In Range("AH2", Range("AH" & Rows.Count).End(xlUp))
        With Range("A:A").SpecialCells(xlCellTypeConstants)
            For i = 1 To .Areas.Count
                If .Areas(i).Cells(1) = WorksheetFunction.Text(CDate(rDate), "mmmm") Then
                    Set srchRng = .Areas(i).Resize(.Areas(i).Cells.Count)
                    For Each rng In srchRng
                        If rng = rDate.Offset(, 2) Then
                            Set rDay = Rows(.Areas(i).Cells(2).Row).Find(Day(CDate(rDate)), LookIn:=xlValues, lookat:=xlWhole)
                            If Not rDay Is Nothing Then
                                If rDate.Offset(, 1).Value < 1 Then num = 1 Else num = rDate.Offset(, 1).Value
                                Cells(rng.Row, rDay.Column).Resize(, num) = "X"
                            End If
                        End If
                    Next rng
                End If
            Next i
        End With
    Next rDate
    Application.ScreenUpdating = True
End Sub