Finacial Year

[mick2]

Hi!
I have been working on this problem for ages;

Given that a Financial Year, spans two Calendar Years (from 1 July through to 30 June inclusive), I am tyring to get Excel to cyclically:

Display the number of calendar days remaining in a Financial Year (beyond Today()).
Display the number of calendar days elapsed in a Financial Year (prior to Today()).

Any suggestions?

Regards,
Mick.

[mangesh_yadav]

Display the number of calendar days remaining in a Financial Year (beyond Today()).

=DATE(YEAR(TODAY())-IF(MONTH(TODAY())>6,1,0),7,0)-TODAY()



Display the number of calendar days elapsed in a Financial Year (prior to Today()).

=TODAY()-DATE(YEAR(TODAY())-IF(MONTH(TODAY())<7,1,0),7,1)


Mangesh

[mick2]

"Thank you, Mangesh!"

Your formulae, are exactly what I was striving for; They are perfect!

Regards,
Mick.

[Niek Otten]

=DATE(YEAR(TODAY())+IF(MONTH(TODAY())<7,0,1),7,1)-TODAY()
=TODAY()-DATE(YEAR(TODAY())-IF(MONTH(TODAY())<7,1,0),7,1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"mick2" <[email protected]> wrote in
message news:[email protected]...
>
> Hi!
> I have been working on this problem for ages;
>
> Given that a Financial Year, spans two Calendar Years (from 1 July
> through to 30 June inclusive), I am tyring to get Excel to
> -cyclically-:
>
> Display the number of calendar days remaining in a Financial
> Year (beyond *Today()*).
> Display the number of calendar days elapsed in a Financial Year (prior
> to *Today()*).
>
> Any suggestions?
>
> Regards,
> Mick.
>
>
> --
> mick2
> ------------------------------------------------------------------------
> mick2's Profile:
> http://www.excelforum.com/member.php...o&userid=24143
> View this thread: http://www.excelforum.com/showthread...hreadid=378027
>

[JE McGimpsey]

One way:

days remaining:

=DATE(YEAR(DATE(YEAR(TODAY()),MONTH(TODAY())+6,1)),6,30)-TODAY()

days elapsed:

=TODAY()- DATE(YEAR(DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)),6,30)


In article <[email protected]>,
mick2 <[email protected]> wrote:

> Hi!
> I have been working on this problem for ages;
>
> Given that a Financial Year, spans two Calendar Years (from 1 July
> through to 30 June inclusive), I am tyring to get Excel to
> -cyclically-:
>
> Display the number of calendar days remaining in a Financial
> Year (beyond *Today()*).
> Display the number of calendar days elapsed in a Financial Year (prior
> to *Today()*).
>
> Any suggestions?
>
> Regards,
> Mick.

[Dave Peterson]

You can get the last fiscal day of the current year with a formula like:

=DATE(YEAR(TODAY())+(MONTH(TODAY())>6),7,0)

Use the year from today's date unless we're in July to December. It also uses
this: the 0th date of July is the last day of June.

Then you could just subtract that from Today().

=DATE(YEAR(TODAY())+(MONTH(TODAY())>6),7,0) - TODAY()
(Format as General)

or maybe...
=DATE(YEAR(TODAY())+(MONTH(TODAY())>6),7,1) - TODAY()

to get the number of days left.

Is there 1 or 0 days left on June 30th?

And to get the first day of the fiscal year, you can use this formula:

=DATE(YEAR(TODAY())-(MONTH(TODAY())<7),7,1)

Then just subtract the two values (and format as General).







mick2 wrote:
>
> Hi!
> I have been working on this problem for ages;
>
> Given that a Financial Year, spans two Calendar Years (from 1 July
> through to 30 June inclusive), I am tyring to get Excel to
> -cyclically-:
>
> Display the number of calendar days remaining in a Financial
> Year (beyond *Today()*).
> Display the number of calendar days elapsed in a Financial Year (prior
> to *Today()*).
>
> Any suggestions?
>
> Regards,
> Mick.
>
> --
> mick2
> ------------------------------------------------------------------------
> mick2's Profile: http://www.excelforum.com/member.php...o&userid=24143
> View this thread: http://www.excelforum.com/showthread...hreadid=378027

--

Dave Peterson

[HRH GG]

Hi,

I was wondering, the formula for calculating the days left in a financial year is great, but is there a way I could change it to calculate the months remaining?

[FDibbins]

Hi and welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.