Finacial Year

[mick2]

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

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

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

Any suggestions?

Regards,
Mick

 

[mangesh_yadav]

Display the number of calendar days remaining in a Financial Yea
(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 (prio
to Today()).

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


Manges

 

[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]

"Thank you, Mangesh!"

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

Regards,
Mick

 

[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)

 

[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).