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!

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)

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


RJohnson701CTS wrote:
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!

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" wrote:

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


RJohnson701CTS wrote:
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!

Hi

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

Regards

Roger Govier


RJohnsonHC wrote:
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" wrote:


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


RJohnson701CTS wrote:

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!

Give the man a cigar! Thank you, sir! :-)

"Roger Govier" wrote:

Hi

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

Regards

Roger Govier


RJohnsonHC wrote:
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" wrote:


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


RJohnson701CTS wrote:

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!

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).

RJohnson701CTS wrote:

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 Peterson

Ooooh. I should have read your post better.

Dave Peterson wrote:

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).

RJohnson701CTS wrote:

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 Peterson

--

Dave Peterson