PaulyK
Board Regular
- Joined
- Aug 27, 2015
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- Web
HI,
I have rejigged a Table I previously used to Spread an Amount equally over a 12 Month Period. This was for ££££. I am now trying to repurpose this to be able to spread a set number of expected work days (1-330) across a set period of months (1-18) See example
The issue I am having is that there is a maximum amount of Working Days per month but I am not sure how to include that (so the days shown are not more than the available days)
Table
Lookup Tables
Is it possible? The end goal beyond this is to create a formula whereby by entering the number of available days and a start and end date; the formula will spread through the months columns equally between the 2 dates (without exceeding the max available days)
I have rejigged a Table I previously used to Spread an Amount equally over a 12 Month Period. This was for ££££. I am now trying to repurpose this to be able to spread a set number of expected work days (1-330) across a set period of months (1-18) See example
The issue I am having is that there is a maximum amount of Working Days per month but I am not sure how to include that (so the days shown are not more than the available days)
Table
| Project Spread.xlsm | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
| 3 | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | |||||
| 4 | Amount | 123 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | |||
| 5 | Months | 5 | 0.0 | 0.0 | 0.0 | 0.0 | 24.6 | 24.6 | 24.6 | 24.6 | 24.6 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |||
| 6 | Start | 5 | |||||||||||||||||||||
| 7 | |||||||||||||||||||||||
Spread of days (example) | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E5:V5 | E5 | =$C$4/$C$5*AND(E4>=$C$6,E4<($C$6+$C$5)) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| amount | ='Spread of days (example)'!$C$4 | E5:V5 |
| month | ='Spread of days (example)'!$C$5 | E5:V5 |
| 'Spread of days (example)'!months | ='Spread of days (example)'!$C$5 | E5:V5 |
| start | ='Spread of days (example)'!$C$6 | E5:V5 |
Lookup Tables
| Project Spread.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Maximum Number of Workdays per month | ||||||||
| 2 | Month Start | Month End | Max Days | Upcoming bank holidays in England and Wales | |||||
| 3 | 01-Mar-22 | 31-Mar-22 | 23 | Date | Day | Description | |||
| 4 | 01-Apr-22 | 30-Apr-22 | 19 | 15-Apr '22 | Friday | Good Friday | |||
| 5 | 01-May-22 | 31-May-22 | 21 | 18-Apr '22 | Monday | Easter Monday | |||
| 6 | 01-Jun-22 | 30-Jun-22 | 20 | 02-May '22 | Monday | Early May bank holiday | |||
| 7 | 01-Jul-22 | 31-Jul-22 | 21 | 02-Jun '22 | Thursday | Spring bank holiday | |||
| 8 | 01-Aug-22 | 31-Aug-22 | 22 | 03-Jun '22 | Friday | Platinum Jubilee bank holiday | |||
| 9 | 01-Sep-22 | 30-Sep-22 | 22 | 29-Aug '22 | Monday | Summer bank holiday | |||
| 10 | 01-Oct-22 | 31-Oct-22 | 21 | 26-Dec '22 | Monday | Boxing Day | |||
| 11 | 01-Nov-22 | 30-Nov-22 | 22 | 27-Dec '22 | Tuesday | Christmas Day (substitute day) | |||
| 12 | 01-Dec-22 | 31-Dec-22 | 20 | 02-Jan '23 | Monday | New Year’s Day (substitute day) | |||
| 13 | 01-Jan-23 | 31-Jan-23 | 21 | 07-Apr '23 | Friday | Good Friday | |||
| 14 | 01-Feb-23 | 28-Feb-23 | 20 | 10-Apr '23 | Monday | Easter Monday | |||
| 15 | 01-Mar-23 | 31-Mar-23 | 23 | 01-May '23 | Monday | Early May bank holiday | |||
| 16 | 01-Apr-23 | 30-Apr-23 | 18 | 29-May '23 | Monday | Spring bank holiday | |||
| 17 | 01-May-23 | 31-May-23 | 21 | 28-Aug '23 | Monday | Summer bank holiday | |||
| 18 | 01-Jun-23 | 30-Jun-23 | 22 | 25-Dec '23 | Monday | Christmas Day | |||
| 19 | 01-Jul-23 | 31-Jul-23 | 21 | 26-Dec '23 | Tuesday | Boxing Day | |||
| 20 | 01-Aug-23 | 31-Aug-23 | 22 | 27-Dec '22 | Tuesday | Christmas Day (substitute day) | |||
| 21 | 01-Sep-23 | 30-Sep-23 | 21 | 02-Jan '23 | Monday | New Year’s Day (substitute day) | |||
| 22 | 01-Oct-23 | 31-Oct-23 | 22 | ||||||
| 23 | 01-Nov-23 | 30-Nov-23 | 22 | ||||||
| 24 | 01-Dec-23 | 31-Dec-23 | 19 | ||||||
| 25 | 01-Jan-24 | 31-Jan-24 | 23 | ||||||
Lookup | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3:C25 | C3 | =NETWORKDAYS(A3,B3,Holidays5[Date]) |
Is it possible? The end goal beyond this is to create a formula whereby by entering the number of available days and a start and end date; the formula will spread through the months columns equally between the 2 dates (without exceeding the max available days)
