I need to create a formula that displays how many days a person has worked in a month, for the month to date. I'm getting my data from someone elses's spreadsheet (example below) so I can't really go in and change it as I'd like. Sample data from the spreadsheet:
Doing a COUNTIF of ("*-*") gives me the number of days worked for the whole month. However I need it for MTD. I've tried to build in a formula that uses NETWORKDAYS to calculate the figure, but if someone has holidays booked later in the month than today's date then it doesn't work.
Row 9 is an example. The total days worked should be 11 but my calculation has it as 7.
Any ideas on how I can get round this?
Copy of New Scheduler Jan 05.xls | |||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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 | |||
8 | D/O | 12.00-8.00 | D/O | 12.00-4.00 | 12.00-8.00 | 9.00-5.00 | D/O | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | D/O | D/O | 12.00-8.00 | 12.00-8.00 | D/O | hols | hols | hols | D/O | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | D/O | D/O | 9.00-5.00 | 8.00-4.30 | 8.00-4.30 | 9.00-5pm | D/O | 13 | 13 | ||
9 | D/O | hols | D/O | hols | hols | hols | D/O | hols | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | D/O | D/O | 8.00-4.00 | 8.00-4.00 | D/O | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | D/O | 8.00-4.00 | 8.00-4.00 | hols | hols | hols | D/O | D/O | hols | 8.00-4.30 | 8.00-4.30 | 9.00-5pm | D/O | 7 | 7 | ||
10 | D/O | sick | D/O | sick | sick | sick | D/O | sick | sick | sick | sick | sick | D/O | D/O | sick | sick | D/O | sick | sick | sick | D/O | sick | sick | sick | sick | sick | D/O | D/O | sick | 8.00-4.30 | 8.00-4.30 | 9.00-5pm | D/O | 0 | 0 | ||
11 | D/O | 12.00-8.00 | D/O | sick | 12.00-8.00 | 9.00-5.00 | D/O | 8.00-1.00 | hols | sick | 8.00-4.00 | 8.00-4.00 | D/O | D/O | 12.00-8.00 | D/O | 12.00-8.00 | 12.00-8.00 | 12.00-8.00 | 9.00-5.00 | D/O | 8.00-4.00 | hols | 8.00-4.00 | 8.00-4.00 | 8.00-4.00 | D/O | D/O | 9.00-5.00 | 8.00-4.30 | 8.00-4.30 | 9.00-5pm | D/O | 12 | 12 | ||
FULL TIMERS |
Doing a COUNTIF of ("*-*") gives me the number of days worked for the whole month. However I need it for MTD. I've tried to build in a formula that uses NETWORKDAYS to calculate the figure, but if someone has holidays booked later in the month than today's date then it doesn't work.
Row 9 is an example. The total days worked should be 11 but my calculation has it as 7.
Any ideas on how I can get round this?