Formula to get the inclusive dates in between start and end date

Hi,

I need some help to figure out what formula should I use for this kind of situation.

I have a table where I assigned Months (Jan-Dec), Current Month YTD (e.g. Nov YTD) and Dec YTD in the column field. Company Name and Year in rows.

For a particular company for example, Co.AAA. The Start Date is Oct 1, 2009 and the contract will end on Sep 30, 2014.

If my current reporting month is Nov 2013, From the table, I need to get the sum up the data that correspond from this dates inclusive in start and end date of Co.AAA.

Inclusive Dates are:
Dec YTD 2013
Dec YTD 2012
Dec YTD 2011
Dec YTD 2010
Dec 2009
Nov 2009
Oct 2009


If this is not possible, what other solution I can use to get the correct number?

Thanks!

Hi, could you upload a sample file with expected result and description/comment?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi,

Attached is the sample workbook.

The only given data are reporting year, reporting month, start date, end date of each company name and the table. Inclusive dates were based on the start and end date.

So for example: If my reporting month is Nov 2013 and Co.AAA start date is Oct 1, 2009 and end date is Sep 1, 2014, the inclusive dates should be Dec YTD 2013, Dec YTD 2012, Dec YTD 2011, Dec YTD 2010, Dec 2009, Nov 2009, Oct 2009 and Sep 2009.

As you have noticed, since my reporting date is Nov 2013, Dec YTD of the current reporting year is always included down to when the company started in Sep 2009.

Another example, Co.BBB's start date is Nov 2011 and end date is Mar 2015. the inclusive dates are: Dec YTD 2013, Dec YTD 2012, Dec 2011 and Nov 2011.

Thanks in advance for the help!

In your spreadsheet example Co. AAA which start date is 1-Oct-09 and End date 30-Sep-14, why Sep 2009 included?
If reporting year is 2014 and reporting month is Feb, what dates shall be inclusive for Co. AAA with start/end date remain intact?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi,

Sorry please exclude Sep 2009 in the spreadsheet and this will be the correct inclusive dates:Nov YTD 2013 (which we use the reporting month YTD and year), Dec YTD 2012, Dec YTD 2011, Dec YTD 2010, Dec 2009, Nov 2009 and Oct 2009.

If reporting year is 2014 and reporting month is Feb, the inclusive dates will be
Feb YTD 2014 (reporting month YTD and reporting year), Dec YTD 2013, Dec YTD 2012, Dec YTD 2011, Dec YTD 2010, Dec 2009, Nov 2009 and Oct 2009.

In the spreadsheet, if you get the value that corresponds to each inclusive dates, you'll get the andswer. But the formula should be applicable with all other company names with start and end date.

Something fishy with your Nov YTD, why need this field if it's always same as Dec YTD? I thought Nov YTD should be sum of Jan to Nov?
Using your inclusive dates, I got the sum total of -612,086.84, but if I do a manual sum for Co. AAA from 1-Oct-09 till 30-Sep-14 I got -570,200.94

Try this formula that sum the range for each month (instead of getting it from Nov/Dec YTD):

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi,

If this can't be possible, what about this one: Using the Jan-Dec column (forget about the YTD columns) and Co.AAA and years in rows, Start Date is still Oct,2009 and my reporting date is Nov 2013. I need to get the sum of:
Oct-Dec 2009
Jan-Dec 2010
Jan-Dec 2011
Jan-Dec 2012
Jan-Nov 2013.

Hi krazyhype19, did you try out the solution that I proposed in post#6?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi Alvin,

Thanks! It works! Sorry I didn't notice your reply so early to send a follow up... Thanks very much!

No worries.
You're welcome, and thanks for the feedback



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin