Planning Board that deducts holidays when added

[Rookie1986]

Hi All,

I hoping for some advice on how to make a team planning board which reduces the employees holiday entitlement when a holiday is added to the board.

Please see below picture to get an idea of what I am looking to create - the sheet only needs to capture holidays as a reducing value (they can only be take as full or 1/2 days).




If anyone can offer some assistance it would be appreciated.

Thanks.

 

[Perpa]

Rookie,
So if I understand correctly, you want to reduce the values in the range C4:C11 by the
'Used' figures in the range D4:D11.

My first thought is you might want to allow for additional employees to be added.
With that in mind, maybe put the 'Holiday Usage 2019' table to the left of the 'January 2018' you now have in the merged cells in A14:B15. That would entail inserting 3 columns.
February and suceeding months would start below 'January' in column D.

From your table currently shown in the range G2:Q7, you want to count the colored holidays for each employee. There appear to be 3 'types' of holidays...'Holidays', 'Public Holidays', and 'Holidays Carried'.

I assume you will manually enter the initial values for the 'Remaining' values, and when the various holidays have been filled in that month, you want to run a macro to capture the 'Used' value for each employee and then adjust the 'Remaining' column accordingly.

You can get the color index value for each of the 3 holiday fill colors you intend to use by selecting the cell whose color is currently in column K and running this macro...

Sub GetColorIndex()
     i = Activecell.Interior.ColorIndex
     Msgbox "The colorindex is " & i
End Sub

From the image you posted, the index colors appear to be Red = 3, Blue = 8 or 28 or 33, and Purple = 21 or 47. You will need to select a cell of each fill color and run the above macro to confirm the colors you used. Then using the color index for each of the 3 colors, a macro can designed to go down the row for each employee and count the number of days each color is used and then sum the total 'Used' days.

This will only count full days, not half days. Had you given any thought how you might show half days?
One idea would be to put 'H' in the colored cells where a half day holiday is located.
then reduce the 'Used' days by half the count of the number of H's for that employee.

The row number for each employee for each month will need to be determined. This should be dynamic
to allow for adding or deleting an employee in a given month. I assume your table will include all twelve months...Yes? So it is important to know in which column(s) the employee name will reside.
Your example shows only the first name in the 'Holiday Usage 2019' table...what if you have two or more
employees with the same first name? You may want to include the last name in your 'Holiday Usage 2019' table.

Those are just some of the things you might consider.
Perpa

 

[Rookie1986]

Rookie,
So if I understand correctly, you want to reduce the values in the range C4:C11 by the
'Used' figures in the range D4:D11.

My first thought is you might want to allow for additional employees to be added.
With that in mind, maybe put the 'Holiday Usage 2019' table to the left of the 'January 2018' you now have in the merged cells in A14:B15. That would entail inserting 3 columns.
February and suceeding months would start below 'January' in column D.

From your table currently shown in the range G2:Q7, you want to count the colored holidays for each employee. There appear to be 3 'types' of holidays...'Holidays', 'Public Holidays', and 'Holidays Carried'.

I assume you will manually enter the initial values for the 'Remaining' values, and when the various holidays have been filled in that month, you want to run a macro to capture the 'Used' value for each employee and then adjust the 'Remaining' column accordingly.

You can get the color index value for each of the 3 holiday fill colors you intend to use by selecting the cell whose color is currently in column K and running this macro...

Sub GetColorIndex()
     i = Activecell.Interior.ColorIndex
     Msgbox "The colorindex is " & i
End Sub

From the image you posted, the index colors appear to be Red = 3, Blue = 8 or 28 or 33, and Purple = 21 or 47. You will need to select a cell of each fill color and run the above macro to confirm the colors you used. Then using the color index for each of the 3 colors, a macro can designed to go down the row for each employee and count the number of days each color is used and then sum the total 'Used' days.

This will only count full days, not half days. Had you given any thought how you might show half days?
One idea would be to put 'H' in the colored cells where a half day holiday is located.
then reduce the 'Used' days by half the count of the number of H's for that employee.

The row number for each employee for each month will need to be determined. This should be dynamic
to allow for adding or deleting an employee in a given month. I assume your table will include all twelve months...Yes? So it is important to know in which column(s) the employee name will reside.
Your example shows only the first name in the 'Holiday Usage 2019' table...what if you have two or more
employees with the same first name? You may want to include the last name in your 'Holiday Usage 2019' table.

Those are just some of the things you might consider.
Perpa

Hi Perpa,

Thank you for the reply

This looks like it would cover what I'm trying to achieve so I'll give it a try and see how I get on.

Thanks again!