To automatically give fiscal period/fiscal year

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.

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Turnipboy" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> Turnipboy
> ------------------------------------------------------------------------
> Turnipboy's Profile:
http://www.excelforum.com/member.php...o&userid=24527
> View this thread: http://www.excelforum.com/showthread...hreadid=502580
>

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?

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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?
>
>
> --
> Turnipboy
> ------------------------------------------------------------------------
> Turnipboy's Profile:
http://www.excelforum.com/member.php...o&userid=24527
> View this thread: http://www.excelforum.com/showthread...hreadid=502580
>

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.

=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" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> Turnipboy
> ------------------------------------------------------------------------
> Turnipboy's Profile:
http://www.excelforum.com/member.php...o&userid=24527
> View this thread: http://www.excelforum.com/showthread...hreadid=502580
>

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

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]...
>
> Thanks, works great and very elegant (much better than my attempts)
>
>
> --
> Turnipboy
> ------------------------------------------------------------------------
> Turnipboy's Profile:
> http://www.excelforum.com/member.php...o&userid=24527
> View this thread:
> http://www.excelforum.com/showthread...hreadid=502580
>