Formula for Date Range 1

[shelby55]

Hello

I am using Excel 2003.

I have a workbook with one sheet of raw data and then various graphs and summary worksheets developed from that. There is always one year of data in the raw worksheet.

I want to be able to compute a "year to date" total which I will do with sumproduct but I have to determine the range first. I only want fiscal year (so starting at April 1) but I don't want to have to hard code this field. With one year of data in the file, that means that I will have some from the previous year i.e. Nov 24 2010 will have back to Nov 25 2009 in the raw data but I only want to compute Apr 1 2010 to Nov 24 2010.

I have a named range for the user selected data but how can I discern the beginning of the fiscal year from that? Thanks.

 

[SkipVought]

hi,

part of the problem is that your data is segmented, as there is only one year's worth of data per sheet. But you can work aroud it by adding your sumproduct results.

I would put the bounding dates in two cells, lets say A1 & A2.
[tt]
=SUMPRODUCT((YourDateRange>=A1)*(YourDateRange<=A2)*(YourSumRange))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks for the reply. I actually already had that part figured out but what I don't have figured out is how to programatically select April 1, 2010 as the minimum date. The maximum date is the "dateselected" named range.

I don't want to have to hard code April 1, 2010 because this workbook is always being added to and next year I want the April 1, 2011 to be the minimum date. Any ideas?

Thanks.

 

[SkipVought]

so you code a formula that will return 4/1/2010 between certain dates.

For instance, this formula returns the current MONDAY...
[tt]
=INT((TODAY()-2)/7)*7+2
[/tt]
7 relates to a WEEK.

You might look at the DATE() function that takes YEAR, MONTH, DAY as arguments.

At what date would the formula return the next fiscal year?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

The only date I know is the userselected date which is my <= date but I can't figure out how to use that date to get to April 1 of the current fiscal year. April 1, 2010 will be the minimum >= date.

I know that the serial number for April 1, 2010 = 40269 but I haven't been able to figure out a calculation to arrive at that via formula based on the information I know i.e. 365 days in the year, userselected date.

 

[mintjulep]

April 1 of "this year" is simply =DATE(YEAR(NOW()),4,1)

To get "last April 1"

=IF(NOW()<=DATE(YEAR(NOW()),4,1),DATE(YEAR(NOW())-1,4,1),DATE(YEAR(NOW()),4,1))

 

[shelby55]

Hi

Great, thanks. So when the year is 2011 past April 1, 2011, will the formula you provided to me change to be that of April 1, 2011?

Thanks.

 

[mintjulep]

Yep.

 

[shelby55]

Thanks so much!!