Increasing financial years

[Elijah]

Hi,

I have the following year typed into a cell: 1999-00 and I'd like the next
cell to be the next year - eg 2000-01.

Can anyone help with a formula to find each financial year?

thanks

Elijah

 

[Norman Harker]

Hi Elijah!

Can you enter A1:
01-1999
Custom Format yyyy-mm

Then in B1:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
Custom format yyyy-mm

The trouble is that 1999-01 is not recognised as a date, whereas
01-1999 is recognised as 1-Jan-1999

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob Phillips]

Hi Elijah,

Is this what you mean

=LEFT(A1,4)+1&"-"&TEXT(RIGHT(A1,2)+1,"00")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Elijah]

Thanks for the quick reply Norman, but I'm looking to use these years
'2001-02' as a basis of a lookup.

So far I've got the following function:

=TEXT(LEFT(C3,4)+1,"0")

which gives me - say 2001 (note C3 is 2000-01); I haven't figured out how to
add on the '-02. Can you help with this?

cheers

Elijah

 

[Elijah]

Yes - that's what I'm looking for. I was half way there though.

Thanks for the quick response.

Elijah

 

[Norman Harker]

Hi Elijah!

I get you now. I read the post as containing an error:

Try:
=LEFT(A1,4)+1&"-"&TEXT(RIGHT(A1,2)+1,"00")

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Elijah]

Going on from this - how do I go about making sure the 12 cells below
are the date format: Jul-00 etc. based on the particular financial
calculated?

Any help, once again, appreciated

Elijah

 

[Bob Phillips]

Hi again Elijah,
Is this it?

=CHOOSE(ROW()-ROW(A$1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep"
,"Oct","Nov","Dec")&"-"&TEXT(MID(A$1,3,2)+ROW()-ROW(A$1),"00")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Elijah]

Hi Bob,

That formula didn't seem to work.

I guess what I'm looking for is to have 12 cells based on the financial year
entered. For example, in reference to 2001-02 (entered in A1), then I would
want in the 12 cells (in B2:B14) a date corresponding to the start of the
month e.g., 01/07/01, 01/08/01, 01/09/01, 01/10/01, 01/11/01, 01/12/01,
01/01/02 ..

So the months will have to change depending on the financial year entered.

thanks again

Elijah