To automatically give fiscal period/fiscal year

[Turnipboy]

I need to put the current fiscal period and year into a cell. Our fiscal
year starts in October, which is period 1, November is period 2, through
to September which is period 12. As the fiscal year runs October -
September, it is named after the year it mostly falls in, so for
instance Dec 2005 is 3/2006 (period/fiscal year).

Is there a way to automatically put the period and year in a cell (it
is normally written in the form period/fiscal year - but this is not
essential), by adapting the =TODAY() function or otherwise.

Thanks.

 

[Bob Phillips]

=TEXT(DATE(YEAR(A15),MONTH(A15)+3,1),"m/yyyy")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Turnipboy]

Thanks it works just as I had hoped.

Could you explain how the formula gives the year as I requested, as I
cannot figure out why it does not just give the same year that is in
cell A15?

 

[Bob Phillips]

Because I add 3 months to it, when I do MONTH(A15)+3, so that Oct 2005
becomes Jan 2006 in the formula that is formatted.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Turnipboy]

Thanks. I now have the current fiscal period and fiscal year in a cell
in the above form (period/fiscal year). I also have a cell with
indicates when the speadsheet was last updated in terms of
period/fiscal year (i.e. the same format). How can I get a cell to say
"Please update" if the spreadsheet has not been updated for six months.

 

[Bob Phillips]

=IF(DATE(RIGHT(D10,4),LEFT(D10,FIND("/",D10)-1)+6,1)<DATE(RIGHT(C10,4),LEFT(
C10,FIND("/",C10)-1),1),"Please update","")

just change the cells to your two.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Turnipboy]

Thanks, works great and very elegant (much better than my attempts)

 

[Roger Govier]

Hi

Using Bob's idea, you could make life a little simpler.
Custom format your cells Format>Cells>Number>Custom> m/yyyy
Then to fix the month 3 months on, use Bob's formula without the Text()
function
=DATE(YEAR(A16),MONTH(A16)+3,1)

which then simplifies the next formula to
=IF(MONTH(D10)-MONTH(C10)>6,"Please Update","")

--
Regards

Roger Govier


"Turnipboy" <[email protected]>
wrote in message
news:[email protected]...