Spread Number of Days Worked Equally Throughout Period of Time

P

PaulyK

Board Regular
Joined
Aug 27, 2015
Messages
50
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. 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
Project Spread.xlsm
BCDEFGHIJKLMNOPQRSTUV
3JanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJun
4Amount123123456789101112131415161718
5Months50.00.00.00.024.624.624.624.624.60.00.00.00.00.00.00.00.00.0
6Start5
7
Spread of days (example)
Cell Formulas
RangeFormula
E5:V5E5=$C$4/$C$5*AND(E4>=$C$6,E4<($C$6+$C$5))
Named Ranges
NameRefers ToCells
amount='Spread of days (example)'!$C$4E5:V5
month='Spread of days (example)'!$C$5E5:V5
'Spread of days (example)'!months='Spread of days (example)'!$C$5E5:V5
start='Spread of days (example)'!$C$6E5:V5


Lookup Tables
Project Spread.xlsm
ABCDEFG
1Maximum Number of Workdays per month
2Month StartMonth EndMax DaysUpcoming bank holidays in England and Wales
301-Mar-2231-Mar-2223DateDayDescription
401-Apr-2230-Apr-221915-Apr '22FridayGood Friday
501-May-2231-May-222118-Apr '22MondayEaster Monday
601-Jun-2230-Jun-222002-May '22MondayEarly May bank holiday
701-Jul-2231-Jul-222102-Jun '22ThursdaySpring bank holiday
801-Aug-2231-Aug-222203-Jun '22FridayPlatinum Jubilee bank holiday
901-Sep-2230-Sep-222229-Aug '22MondaySummer bank holiday
1001-Oct-2231-Oct-222126-Dec '22MondayBoxing Day
1101-Nov-2230-Nov-222227-Dec '22TuesdayChristmas Day (substitute day)
1201-Dec-2231-Dec-222002-Jan '23MondayNew Year’s Day (substitute day)
1301-Jan-2331-Jan-232107-Apr '23FridayGood Friday
1401-Feb-2328-Feb-232010-Apr '23MondayEaster Monday
1501-Mar-2331-Mar-232301-May '23MondayEarly May bank holiday
1601-Apr-2330-Apr-231829-May '23MondaySpring bank holiday
1701-May-2331-May-232128-Aug '23MondaySummer bank holiday
1801-Jun-2330-Jun-232225-Dec '23MondayChristmas Day
1901-Jul-2331-Jul-232126-Dec '23TuesdayBoxing Day
2001-Aug-2331-Aug-232227-Dec '22TuesdayChristmas Day (substitute day)
2101-Sep-2330-Sep-232102-Jan '23MondayNew Year’s Day (substitute day)
2201-Oct-2331-Oct-2322
2301-Nov-2330-Nov-2322
2401-Dec-2331-Dec-2319
2501-Jan-2431-Jan-2423
Lookup
Cell Formulas
RangeFormula
C3:C25C3=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)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
i don't understand what you're trying to do
Map1
ABCDEFGHIJKLMNOPQR
101-01-2201-02-2201-03-2201-04-2201-05-2201-06-2201-07-2201-08-2201-09-2201-10-2201-11-2201-12-2201-01-2301-02-2301-03-23
2
3
4startstopworkdaysmonth1month2month3month4month5month6month7month8month9month10month11month12month13
5di 15/02/22di 13/12/2221001023192120212222212290210
6
7
8Upcoming bank holidays in England and Wales
9DateDayDescription
10vrijdag 15 april 2022FridayGood Friday
11maandag 18 april 2022MondayEaster Monday
12maandag 2 mei 2022MondayEarly May bank holiday
13donderdag 2 juni 2022ThursdaySpring bank holiday
14vrijdag 3 juni 2022FridayPlatinum Jubilee bank holiday
15maandag 29 augustus 2022MondaySummer bank holiday
16maandag 26 december 2022MondayBoxing Day
17dinsdag 27 december 2022TuesdayChristmas Day (substitute day)
18maandag 2 januari 2023MondayNew Year’s Day (substitute day)
19vrijdag 7 april 2023FridayGood Friday
20maandag 10 april 2023MondayEaster Monday
21maandag 1 mei 2023MondayEarly May bank holiday
22maandag 29 mei 2023MondaySpring bank holiday
23maandag 28 augustus 2023MondaySummer bank holiday
24maandag 25 december 2023MondayChristmas Day
25dinsdag 26 december 2023TuesdayBoxing Day
26dinsdag 27 december 2022TuesdayChristmas Day (substitute day)
27maandag 2 januari 2023MondayNew Year’s Day (substitute day)
Blad2
Cell Formulas
RangeFormula
E1:R1E1=+EDATE(D1,1)
C5C5=NETWORKDAYS.INTL(A5,B5,,Blad2!$A$10:$A$27)
D5:P5D5=MAX(0,NETWORKDAYS.INTL(MAX(D$1,$A5),MIN(E$1-1,$B5),,Blad2!$A$10:$A$27))
R5R5=SUM(D5:P5)
 
Upvote 0
BSALV said:
i don't understand what you're trying to do
Map1
ABCDEFGHIJKLMNOPQR
101-01-2201-02-2201-03-2201-04-2201-05-2201-06-2201-07-2201-08-2201-09-2201-10-2201-11-2201-12-2201-01-2301-02-2301-03-23
2
3
4startstopworkdaysmonth1month2month3month4month5month6month7month8month9month10month11month12month13
5di 15/02/22di 13/12/2221001023192120212222212290210
6
7
8Upcoming bank holidays in England and Wales
9DateDayDescription
10vrijdag 15 april 2022FridayGood Friday
11maandag 18 april 2022MondayEaster Monday
12maandag 2 mei 2022MondayEarly May bank holiday
13donderdag 2 juni 2022ThursdaySpring bank holiday
14vrijdag 3 juni 2022FridayPlatinum Jubilee bank holiday
15maandag 29 augustus 2022MondaySummer bank holiday
16maandag 26 december 2022MondayBoxing Day
17dinsdag 27 december 2022TuesdayChristmas Day (substitute day)
18maandag 2 januari 2023MondayNew Year’s Day (substitute day)
19vrijdag 7 april 2023FridayGood Friday
20maandag 10 april 2023MondayEaster Monday
21maandag 1 mei 2023MondayEarly May bank holiday
22maandag 29 mei 2023MondaySpring bank holiday
23maandag 28 augustus 2023MondaySummer bank holiday
24maandag 25 december 2023MondayChristmas Day
25dinsdag 26 december 2023TuesdayBoxing Day
26dinsdag 27 december 2022TuesdayChristmas Day (substitute day)
27maandag 2 januari 2023MondayNew Year’s Day (substitute day)
Blad2
Cell Formulas
RangeFormula
E1:R1E1=+EDATE(D1,1)
C5C5=NETWORKDAYS.INTL(A5,B5,,Blad2!$A$10:$A$27)
D5:P5D5=MAX(0,NETWORKDAYS.INTL(MAX(D$1,$A5),MIN(E$1-1,$B5),,Blad2!$A$10:$A$27))
R5R5=SUM(D5:P5)
Click to expand...

example - If i have 150 Days Allocated for A project I want to be able to spread that out through an equal period of months (not exceeding the total amount of working days for each month - that bit could be simplified to be a standard max of 20 days)

but i think you have achieved that in your example....
 
Upvote 0
You must log in or register to reply here.

Similar threads

vinzent
  • Question
Formula to get the 4th workday before end of month
Replies
8
Views
191
Tetra201
Tetra201
S
  • Solved
lookup return from range of dates
Replies
4
Views
160
Peter_SSs
Peter_SSs
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
271
amolvijay
A
M
  • Question
COUNTIFS formula for number of dates in a quarter??
Replies
4
Views
214
Alex Blakenburg
A
B
  • Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
212
B-radK
B

Forum statistics

Threads
1,215,575
Messages
6,125,618
Members
449,238
Latest member
wcbyers

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top