Hello
I have a holiday booking/entitlement spreadsheet that shows holiday taken and booked for the year. This all combines nicely as a total, but I would really love a running total to see what someone has taken to date. Just so I’m prepared in case someone was to leave unexpectedly.
Researching this I found SUMIFS formula. I added dates to row 1 to then add against amounts in each person’s row, starting from row 9.
Formula in cell NL9 =SUMIFS(G9:BM9,$G$1:$BM$1,">="&$A$2,G9:BM9,"<="&$A$3)
My formula is not working and adding the full row not adjusting to the date, I have no idea why and maybe I am going at this the wrong way.
I’ve included my spreadsheet as its difficult to explain fully. Its reduced in size so I can sample it here.
I’d appreciate any assistance as I’m stuck. Thank you
I have a holiday booking/entitlement spreadsheet that shows holiday taken and booked for the year. This all combines nicely as a total, but I would really love a running total to see what someone has taken to date. Just so I’m prepared in case someone was to leave unexpectedly.
Researching this I found SUMIFS formula. I added dates to row 1 to then add against amounts in each person’s row, starting from row 9.
Formula in cell NL9 =SUMIFS(G9:BM9,$G$1:$BM$1,">="&$A$2,G9:BM9,"<="&$A$3)
My formula is not working and adding the full row not adjusting to the date, I have no idea why and maybe I am going at this the wrong way.
I’ve included my spreadsheet as its difficult to explain fully. Its reduced in size so I can sample it here.
I’d appreciate any assistance as I’m stuck. Thank you
| HOLIDAY BOOKING.xlsx | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 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 | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | BH | BI | BJ | BK | BL | BM | BN | BO | BP | BQ | BR | BS | BT | BU | BV | |||
| 1 | ANNUAL ENTITLEMENT | CARRY OVER | TOTAL TO TAKE | HOLIDAY REMAINING | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | Feb | TOTAL TAKEN | BANK HOLIDAYS | UNPAID LEAVE | MEDICAL | TOTAL TAKEN TO DATE | ESTIMATED ACCRUAL | HOLIDAY START | HOLIDAY END | |||||
| 2 | 01/01/2022 | Jan 22 | Feb 22 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 | 12/02/2022 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 4 | FULL DAY | 1.0 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 5 | HALF DAY OFF | 0.5 | 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 | 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 | |||||||||||||||
| 6 | MEDICAL | T | WK10 | WK11 | WK12 | WK13 | WK14 | WK15 | WK16 | WK17 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 | UNPAID LEAVE | U | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | |||||||||||||||
| 8 | BANK HOLIDAY | BH | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | |||||||||||||||||||||||||||||||||
| 9 | DAVID FROST | 28.0 | 4.0 | 32.0 | 25.0 | BH | 1.0 | 1.0 | 1.0 | U | 1.0 | 1.0 | 1.0 | 6.0 | 1 | 1 | 0 | 6.0 | 3.23 | 01/01/22 | 12/02/22 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 10 | EDDIE LARGE | 28.0 | 0.0 | 28.0 | 19.5 | BH | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.5 | 7.5 | 1 | 0 | 0 | 7.5 | 3.23 | 01/01/22 | 12/02/22 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 11 | TONY COTTEE | 28.0 | 9.0 | 37.0 | 36.0 | BH | 0.0 | 1 | 0 | 0 | - | 3.23 | 01/01/22 | 12/02/22 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 12 | WINONA RYDER | 28.0 | 0.0 | 28.0 | 26.0 | BH | U | 1.0 | 1.0 | 1 | 1 | 0 | 1.0 | 3.23 | 01/01/22 | 12/02/22 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2021 2022 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A3,BV9:BV12 | A3 | =TODAY() |
| AJ5:BL5,I5:AH5 | I5 | =H5+1 |
| BM8,BF8:BJ8,AY8:BC8,AR8:AV8,AK8:AO8,AD8:AH8,W8:AA8,P8:T8,I8:M8 | I8 | =COUNTIF(I9:I12,1) |
| E9:E12 | E9 | =C9+D9 |
| F9:F12 | F9 | =E9-BN9-BO9 |
| BN9:BN12 | BN9 | =SUM(G9:BM9) |
| BO9:BO12 | BO9 | =COUNTIF(G9:BM9,$B$8) |
| BP9:BP12 | BP9 | =COUNTIF(G9:BM9,$B$7) |
| BQ9:BQ12 | BQ9 | =COUNTIF(G9:BM9,$B$6) |
| BR9:BR12 | BR9 | =SUMIFS(G9:BM9,$G$1:$BM$1,">="&$A$2,G9:BM9,"<="&$A$3) |
| BS9:BS12 | BS9 | =DAYS(BV9,BU9)/7*0.538 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F9:F12 | Cell Value | >#REF! | text | NO |
| G8:BM8 | Cell Value | >4 | text | NO |
| G9:BM12 | Cell Value | contains "T" | text | NO |
| G9:BM12 | Cell Value | =0.5 | text | NO |
| G9:BM12 | Cell Value | =1 | text | NO |
| G9:BM12 | Cell Value | contains "BH" | text | NO |
| G9:BM12 | Cell Value | contains "U" | text | NO |
