Finacial Year
 

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

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378027

=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" wrote in
message ...

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

"Thank you, Mangesh!"

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

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

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 ,
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.

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