 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2006Another Date related question....
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. 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 ??  SignatureVBA Noob 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! --------------------------------- > 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 ?? 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 SignatureVBA Noob 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? ---------------------------------------------------------------------- > 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 [quoted text clipped - 4 lines] > > 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 SignatureVBA Noob 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. -------------------------------------------------------------------- > Hi Pete, > [quoted text clipped - 7 lines] > > 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 SignatureVBA Noob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.