Hi all,
I am putting together a Staff planner for my business, I have just under 30 employees.
Each employee has a total 200 hours paid vacation per year. What I am trying to do is make a sort of calendar which is marked with the amount of hours they are scheduled to work each day. See my example below.
I have made some conditional formatting rules to format certain letters if they're entered, these letters represent a reason for absence.
I currently have the letter H set for vacations, which formats the cell green and the text Bold White. I would like to make a formula that counts the 3rd digit of each cell that has a letter H present. So basically my cell would say H-4 meaning the employee had taken 4 hours vacation that day. In another cell I will have their Vacation hours (200) - the days booked, days booked in the planner will say H-8, etc. Once their hours are used the column will show a 0.
I think this should be possible, tried a few formulas but just can't seem to get it right.
Employee 1 for example would count at 24 or 200 - 24 = 176
Employee 2 for example would count at 24 or 200 - 20 = 180
Hope this makes sense.
I am putting together a Staff planner for my business, I have just under 30 employees.
Each employee has a total 200 hours paid vacation per year. What I am trying to do is make a sort of calendar which is marked with the amount of hours they are scheduled to work each day. See my example below.
I have made some conditional formatting rules to format certain letters if they're entered, these letters represent a reason for absence.
I currently have the letter H set for vacations, which formats the cell green and the text Bold White. I would like to make a formula that counts the 3rd digit of each cell that has a letter H present. So basically my cell would say H-4 meaning the employee had taken 4 hours vacation that day. In another cell I will have their Vacation hours (200) - the days booked, days booked in the planner will say H-8, etc. Once their hours are used the column will show a 0.
I think this should be possible, tried a few formulas but just can't seem to get it right.
Employee 1 for example would count at 24 or 200 - 24 = 176
Employee 2 for example would count at 24 or 200 - 20 = 180
Hope this makes sense.
2021 Staffing Plan.xlsm | |||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | |||
1 | Summary | Approved Vacation to Date | January 2021 Q1 | ||||||||||||||||||||||||||||||||||||||
2 | S | SICK | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | ||||
3 | C | COVID-19 | |||||||||||||||||||||||||||||||||||||||
4 | V | VAB | |||||||||||||||||||||||||||||||||||||||
5 | A | ATF | |||||||||||||||||||||||||||||||||||||||
6 | P | PARENTAL LEAVE | 28-Dec-20 | 29-Dec-20 | 30-Dec-20 | 31-Dec-20 | 01-Jan-21 | 02-Jan-21 | 03-Jan-21 | 04-Jan-21 | 05-Jan-21 | 06-Jan-21 | 07-Jan-21 | 08-Jan-21 | 09-Jan-21 | 10-Jan-21 | 11-Jan-21 | 12-Jan-21 | 13-Jan-21 | 14-Jan-21 | 15-Jan-21 | 16-Jan-21 | 17-Jan-21 | 18-Jan-21 | 19-Jan-21 | 20-Jan-21 | 21-Jan-21 | 22-Jan-21 | 23-Jan-21 | 24-Jan-21 | 25-Jan-21 | 26-Jan-21 | 27-Jan-21 | 28-Jan-21 | 29-Jan-21 | 30-Jan-21 | 31-Jan-21 | ||||
7 | K | KOMP | |||||||||||||||||||||||||||||||||||||||
8 | U | UNPAID | |||||||||||||||||||||||||||||||||||||||
9 | R | REMOTE | |||||||||||||||||||||||||||||||||||||||
10 | H | VACATION | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||
11 | 01 | Site Manager | Kenneth Backgård | H-8 | H-8 | H-8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | ||||||||||||||||
12 | 02 | Mobilisation PM | Paul Doyle | H-8 | H-4 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | ||||||||||||||||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E10:G10,AG10:AK10,Z10:AD10,S10:W10,O10:P10,L10:M10 | E10 | =SUM(E33:E112)/8 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
AL11:AM32 | Cell Value | contains "" | text | NO |
AL11:AM32 | Cell Value | contains "" | text | NO |
AL11:AM32 | Cell Value | contains "" | text | NO |
AL11:AM32 | Cell Value | contains "" | text | NO |
AL11:AM32 | Cell Value | contains "" | text | NO |
AL11:AM32 | Cell Value | contains "" | text | NO |
AL11:AM32 | Cell Value | contains "" | text | NO |
AL11:AM32 | Cell Value | contains "" | text | NO |
AL11:AM32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
AE11:AF32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
X11:Y32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
Q11:R32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
N11:N32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
H11:K32 | Cell Value | contains "" | text | NO |
NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32,JY19:KC32 | Cell Value | <1 | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
AE10:AF10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
X10:Y10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
Q10:R10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
N10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
H10:K10 | Cell Value | contains "" | text | NO |
NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32,JY19:KC32 | Cell Value | contains "" | text | NO |
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32 | Cell Value | contains "" | text | NO |
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32 | Cell Value | contains "" | text | NO |
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32 | Cell Value | contains "" | text | NO |
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32 | Cell Value | contains "" | text | NO |
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32 | Cell Value | contains "" | text | NO |
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32 | Cell Value | contains "" | text | NO |
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32 | Cell Value | contains "" | text | NO |
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32 | Cell Value | contains "" | text | NO |
Last edited: