Need help with date range

[Guest]

Hi, all. I'm trying to do something that's probably pretty simple, but I
don't know how to do it. I've got a column of dates in my SS. What I want
to do is if a date in the column falls between a certain date range, then in
the column next to the date it gives a two-digit year. For example,
12/31/2005 falls between 7/1/2005 and 7/31/2006, so in the column next to
"12/31/2005" it would say "06" or "2006" or something like that. I want to
be able to do this with several date ranges. (I'm basically trying to
translate dates in fiscal years.) Can anyone give me some tips? Thanks in
advcance!

 

[Dave O]

I got a solution by creating a table of dates and their associated
fiscal years. For instance in A1 I have 7/1/2005; in B1I have
7/31/2006; in C1 I have 2006. Row 2 is for fiscal 2007, row 3 for 2008
and so on thru row 6 (fiscal 2011).

In cell B9 I have the date 12/31/2005; in c9 I have the formula
=SUMPRODUCT(--(B9>=$A$1:$A$6),--(B9<=$B$1:$B$6),$C$1:$C$6)

 

[Roger Govier]

Hi

From the dates given, it looks like a 13 month year!!
I suspect you either meant 7/1/2005 and 6/30/2006 or 8/1/2005 and 7/31/2006

Taking the latter case, and assuming your dates are in column A, in column B
=YEAR(A2)-(MONTH(A2)<8)
Change the 8 to a 7 if the Year begins in July rather than August.
If you only want to show a 2 digit year, format the cells as yy

Regards

Roger Govier

 

[Dave Peterson]

You could probably use a formula that returns the year.

It could be as simple as
=year(a2)

But if your fiscal year isn't from Jan to Dec, you may want to post the dates.

In fact, if you post your rules, you could even get a formula for fiscal quarter
(just in case you ever need it).

 

[Dave Peterson]

Ooooh. I should have read your post better.

You could probably use a formula that returns the year.

It could be as simple as
=year(a2)

But if your fiscal year isn't from Jan to Dec, you may want to post the dates.

In fact, if you post your rules, you could even get a formula for fiscal quarter
(just in case you ever need it).

 

[Guest]

DOH! You're quite right, Roger. I meant to say 6/30/2006! I tired using
your formula, but it actually gives me the year prior to what it should be,
i.e.,

6/30/2006 returns "2005) with formula "=YEAR(J2)-(MONTH(J2)<7)"

My dates are in column J...

Many, many thanks!

-RJ

 

[Roger Govier]

Hi

I read it the wrong way round
Change to
=YEAR(A19)+(MONTH(A19)>6)
Leave the format as General

Regards

Roger Govier

 

[Guest]

Give the man a cigar! Thank you, sir!

Hi

I read it the wrong way round
Change to
=YEAR(A19)+(MONTH(A19)>6)
Leave the format as General

Regards

Roger Govier