Adding fiscal years ...

  • Thread starter Thread starter StargateFanNotAtHome
  • Start date Start date
S

StargateFanNotAtHome

This one here is another one that turned out to be too complicated for
me to figure out. I have two columns in worksheet. In column A I
have to manually type in the fiscal start and end years (as seen
below). The formulas in B automate the rest of B and they're all in
this type of configuration:

=IF(A2<>"",TEXT(DATE(LEFT(A2,4),4,1),"mmm.dd.yyyy\-") & TEXT(DATE(LEFT
(A2,4)+1,4,0),"mmm.dd.yyyy"),"")

(code kindly provided to me by members of this great group some months
or even years back).

Col. A Col. B
2000-2001 Apr.01.2000-Mar.31.2001
2001-2002 Apr.01.2001-Mar.31.2002
2002-2003 Apr.01.2002-Mar.31.2003
2003-2004 Apr.01.2003-Mar.31.2004
2004-2005 Apr.01.2004-Mar.31.2005
2005-2006 Apr.01.2005-Mar.31.2006
2006-2007 Apr.01.2006-Mar.31.2007
2007-2008 Apr.01.2007-Mar.31.2008

I was hoping to automate this even further so that I need to enter
only _one_ fiscal year in yyyy-yyyy format. Though since I need to
modify this workbook, I'd actually now place this formula in an
entirely different cell now, in C1.

Was hoping to get Excel then to show the dates for the previous 5
fiscal years and the following 5 fiscal years in relation to the date
put in C1. Since the format for the cells deals with two dates, via
yyyy-yyyy, that's why I haven't been able to fix this even though I've
spent quite some time searching the archives here.

Would something along the lines of a modified C1+1 to increment the
fiscal years in yyyy-yyyy work or is there a fancy formula
configuration to use <g>? If it's along the lines of C1+1, then I'd
just use that for the rest of the cells in Column A (i.e., C1+2, C1+3,
C1+4 ... ?).

As long as the dependent formula,

=IF(A2<>"",TEXT(DATE(LEFT(A2,4),4,1),"mmm.dd.yyyy\-") & TEXT(DATE(LEFT
(A2,4)+1,4,0),"mmm.dd.yyyy"),"")

still works, anything goes that's "legal", I guess (?).

Thanks for any help re this! :oD
 
If I understand correctly, you want

2000-2001 Apr.01.2001-Mar.31.2002
Apr.01.2002-Mar.31.2003
Apr.01.2002-Mar.31.2003
Apr.01.2003-Mar.31.2004

The years are in A2
In B2 I have
=IF(A2<>"",TEXT(DATE(LEFT($A$2,4)+ROW(A1),4,1),"mmm.dd.yyyy\-") &
TEXT(DATE(LEFT($A$2,4)+1+ROW(A1),4,0),"mmm.dd.yyyy"),"")

Note that Row(A1) computes to 1. When copied down the colum,n, we get
Row(A2), Row(A3)...
So we add 1 each time we move down
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


message
news:2f204dd9-0710-4079-aad6-3fca0163c138@c36g2000yqn.googlegroups.com...
 
Opps!

2000-2001 Apr.01.2000-Mar.31.2001
Apr.01.2001-Mar.31.2002
Apr.01.2002-Mar.31.2003
Apr.01.2003-Mar.31.2004

=IF($A$2<>"",TEXT(DATE(LEFT($A$2,4)+ROW(A1)-1,4,1),"mmm.dd.yyyy\-") &
TEXT(DATE(LEFT($A$2,4)+ROW(A1),4,0),"mmm.dd.yyyy"),"")



OR

2000 Apr.01.2000-Mar.31.2001
Apr.01.2001-Mar.31.2002
Apr.01.2002-Mar.31.2003


=IF($A$2<>"",TEXT(DATE($A$2-1+ROW(A1),4,1),"mmm.dd.yyyy\-") &
TEXT(DATE($A$2+ROW(A1),4,0),"mmm.dd.yyyy"),"")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


message
news:2f204dd9-0710-4079-aad6-3fca0163c138@c36g2000yqn.googlegroups.com...
 
Given the OP's year range format, this slightly shorter formula will work.
Assuming the 2000-2001 year range is in A1, put this in B1 (note the cell)
and copy down as required...

=SUBSTITUTE("Apr.01."&A1,"-","-Mar.31.")

Also, with the 2000-2001 year range in A1, the OP can put this in A2 and
copy down as needed in order top produce the next higher year ranges...

=LEFT(A1,4)+1&"-"&RIGHT(A1,4)+1
 
Boy, I really have to switch over to the MS newsgroups access versus
Google. I lost track of this message so finally last night rememberd
at home to get the particulars from my newsreader. Despite my pretty
unique username, done on purpose so easier to google for posts, Google
surprisingly misses a lot! Anyway, that's a big job but will have to
remember one of these days to start accessing these ngs via MS
instead.

Anyway, finally am able to get back to the group re this. Getting the
re the final results but still had a couple of questions, if I may ...

Bernard Liengme, hi and thanks!
=IF($A$2<>"",TEXT(DATE(LEFT($A$2,4)+ROW(A1)-1,4,1),"mmm.dd.yyyy\-") &
TEXT(DATE(LEFT($A$2,4)+ROW(A1),4,0),"mmm.dd.yyyy"),"")
Click to expand...

This does indeed populate the second column automatically, but column
A is not addressed.
=IF($A$2<>"",TEXT(DATE($A$2-1+ROW(A1),4,1),"mmm.dd.yyyy\-") &
TEXT(DATE($A$2+ROW(A1),4,0),"mmm.dd.yyyy"),"")
Click to expand...

XL didn't like this one <g>. I get #VALUE! in all cells where this
found. So I guess the first one is the one to stick with for column A
best wishes
Click to expand...

Likewise!

:oD
 
Hi, Rick and thanks!

....

Wait a minute, wait a minute ... figured out what was wrong. D'UH!!
<lol> It's the little things that trip us up!

For some reason, I had 2 merged rows in the header row, so I was
actually starting at A3, not A2. That was enough to throw everything
off! No need for them being merged. I must have copied this sheet
from another one and just deleted the other columns!



On Jul 11, 1:09 pm, "Rick Rothstein"
[snip]

Yup, Rick, seems to work like a charm now! What neat formulas these
are. I was able to use the shorter formula just fine and saw how you
treated chaging the years even though they are in tricky yyyy-yyyy
format (tricky for me, at any rate). And I got the 5 years before and
5 years after which I was hoping for, though I decided to go with 6
years before and after instead of just 5.

This will be a big help for me. When they have me modifying/finding
records for a bunch of fiscal years and I'm having to pull files and
folders to retrieve docts for the years they specify, I quickly get
bogged up with keeping the dates all straight in my mind <g>. Problem
resolved. I can whip out this handy dandy Fiscal Years sheet and type
in the starting FY and get a bunch of years before an after and keep
from losing my mind by referring to the list frequently <g>.

So I put in 2000-2001 in the starting cell (which turns out to be E1,
not C1 <g>): in this example, 2000-2001

And I get this in columns A and B with row 8 being the "middle row"
where 2000-2001 is just repeated but with red cell colouring, and
yellow text:

Fiscal Year Actual Fiscal Year Start
and End Dates

1994-1995 Apr.01.1994-Mar.31.1995
1995-1996 Apr.01.1995-Mar.31.1996
1996-1997 Apr.01.1996-Mar.31.1997
1997-1998 Apr.01.1997-Mar.31.1998
1998-1999 Apr.01.1998-Mar.31.1999
1999-2000 Apr.01.1999-Mar.31.2000
---------------------------------------------------------
2000-2001 Apr.01.2000-Mar.31.2001
---------------------------------------------------------
2001-2002 Apr.01.2001-Mar.31.2002
2002-2003 Apr.01.2002-Mar.31.2003
2003-2004 Apr.01.2003-Mar.31.2004
2004-2005 Apr.01.2004-Mar.31.2005
2005-2006 Apr.01.2005-Mar.31.2006
2006-2007 Apr.01.2006-Mar.31.2007

Dancing jig! Can't tell you for how many years I've needed this
sheet! Never thought of using an Excel solution before, though!

Thanks!! :oD
 
Back
Top