Another Date related question....

[Pete Dray]

Hello all,
I am trying to work on a formula using DAYS360 (We cannot use NETWORKDAYS as
plug-in's are'nt allowed at work), and failed! So a 30 day month * 0.67 to
give 4 working weeks is good enough here.
We have a Start Date and an End Date, the variables of this are pretty wide.
The start date could be any time from now until out in 2015.
The end date could be 1 month or 5 years after the start date.
The worksheet could have 2,500 rows, with this formula in, so memory hogging
is an issue.
Between the start and end date, I need to split the number of days (Based on
the DAYS360) in each year that is covered by those dates. Don't forget I
cannot predict which year the start date will be. At my point of failure, I
had worked out the number of days in the Start year, then added 360 per year
until the cumulative total had reached the grand total DAYS360 (With an if
statement to get the correct balance in the end year). But I couldn't work
out the formula for me to allow any start date.
Help! Getting this right saves terrabytes.

 

[VBA Noob]

Can you post a sample data.

E.g if start date is 1/5/04 and end date is 1/3/06 what do you want to
see as the result ??

 

[Pete Dray]

Certainly........
We have columns for each year going out to 2015 (Or however far we need).
From your dates, I need the number of days (Using DAYS360) in 2004 to appear
in the 2004 column, days in 2005 to appear in the 2005 column, days in 2006
to appear in the 2006 column etc. All the other columns must return Zero.
As I said, there could be up to 2,500 rows, each with a potentially seperate
start dat and end date, the formula must allow this.

At the moment, there is a seperate worksheet for each year, so the workbooks
are getting rather large!

 

[VBA Noob]

Sorry to be thick here but is the data relating to a particular row.
Just trying to work out if something starts in 2004 and ended in 2015
where the end date goes ...into the 2015 spreadsheet ?? Also would it
be on the same line as the 2004 start date ??

A sample of data would make things easier


VBA Noob

 

[Pete Dray]

Thanks for sticking with it...your not being thick - it's my explanations.

Yes, each pair of dates is pertaining to a row.

If you please, set this up: -
A2 contains a task start date 1st July 2006
B2 contains a task end date 3rd November 2009
C1..L1 are the year labels 2006 to 2015
C2..L2 is where the magic formula needs to be.
The grand total DAYS360 is 1202 for these two dates, but the formula needs
to put the correct days in each year (Obviously 2007 and 2008 are full years
so 360 will appear there.

A3 contains a task start date 12th December 2007
B3 contains a task end date of 3rd March 2015
The DAYS360 total is 2601, but again I need to know the spread - 2008 to
2014 being full years so 360 will be in those.

A4 has a start of 10th October 2006
B4 has the end date 24th December 2006
DAYS360 tot. is 74 and obviously it's all in 2006

The same formula must be used in all three examples, so it can be ready to
take any start date and any end date......up to 2,500 rows deep.

I hope that makes more sense?

 

[VBA Noob]

Hi Pete,

This formula assumes Sheet 1 has the start and end dates

=MAX(0,(MIN(Sheet1!$B2,DATE(2006,12,31))-MAX(Sheet1!$A2,DATE(2006,1,1))+1))

If it's not what your after try the attached link for ideas

http://www.cpearson.com/excel/distribdates.htm


VBA Noob

 

[Pete Dray]

VBA Noob, that is perfect!
It doesn't work on the "days360", but it doesn't need to for what I actually
want it for.
Many, many thanks.

 

[VBA Noob]

Phew,

I just realised the 360 days think and was having a think how to adapt
the formula. I've had a few drinks so didn't get very far.

I'm sure one of the experts on here would have cracked it

VBA Noob