Minus Display
 

Preamble:
A Financial Year, is from 1 July through to 30 June, inclusive.
The formula (which was offered by this forum, and appears below), is
required to display (countdown) the day(s) remaining in a Financial
Year, on a cyclical basis.
The formula worked well, towards the end of the last Financial Year,
but...


The problem:
Since the beginning (1 July) of this Financial Year, the countdown
display is preceded by a minus (-) character. I know it's trivial, but
it's also irritating...
How do I, remove the minus character?


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

Regards,
Mick.


--
mick2
------------------------------------------------------------------------
mick2's Profile: http://www.excelforum.com/member.php...o&userid=24143
View this thread: http://www.excelforum.com/showthread...hreadid=384021

If all you want is to always have a positive number then wrap the formula in
an ABS function:

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


--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk
"mick2" wrote in
message ...

Preamble:
A Financial Year, is from 1 July through to 30 June, inclusive.
The formula (which was offered by this forum, and appears below), is
required to display (countdown) the day(s) remaining in a Financial
Year, on a cyclical basis.
The formula worked well, towards the end of the last Financial Year,
but...


The problem:
Since the beginning (1 July) of this Financial Year, the countdown
display is preceded by a minus (-) character. I know it's trivial, but
it's also irritating...
How do I, remove the minus character?


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

Regards,
Mick.


--
mick2
------------------------------------------------------------------------
mick2's Profile:
http://www.excelforum.com/member.php...o&userid=24143
View this thread: http://www.excelforum.com/showthread...hreadid=384021

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mick2" wrote in
message ...

Preamble:
A Financial Year, is from 1 July through to 30 June, inclusive.
The formula (which was offered by this forum, and appears below), is
required to display (countdown) the day(s) remaining in a Financial
Year, on a cyclical basis.
The formula worked well, towards the end of the last Financial Year,
but...


The problem:
Since the beginning (1 July) of this Financial Year, the countdown
display is preceded by a minus (-) character. I know it's trivial, but
it's also irritating...
How do I, remove the minus character?


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

Regards,
Mick.


--
mick2
------------------------------------------------------------------------
mick2's Profile:
http://www.excelforum.com/member.php...o&userid=24143
View this thread: http://www.excelforum.com/showthread...hreadid=384021

Mick

It's a simple error in the formula. Flip the sign and it will work:
=DATE(YEAR(TODAY())+IF(MONTH(TODAY())<7,0,1),7,1)-TODAY()

I think you're simpler storing the financial year end in a cell (e.g.
A1), then you can use:
=A1-TODAY()

.... as long as you update A1 once a year.

Best

Andrew

mick2 wrote:
Preamble:
A Financial Year, is from 1 July through to 30 June, inclusive.
The formula (which was offered by this forum, and appears below), is
required to display (countdown) the day(s) remaining in a Financial
Year, on a cyclical basis.
The formula worked well, towards the end of the last Financial Year,
but...


The problem:
Since the beginning (1 July) of this Financial Year, the countdown
display is preceded by a minus (-) character. I know it's trivial, but
it's also irritating...
How do I, remove the minus character?


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

Regards,
Mick.


--
mick2
------------------------------------------------------------------------
mick2's Profile: http://www.excelforum.com/member.php...o&userid=24143
View this thread: http://www.excelforum.com/showthread...hreadid=384021

Thanks folks,
Problem solved!

Regards,
Mick.


--
mick2
------------------------------------------------------------------------
mick2's Profile: http://www.excelforum.com/member.php...o&userid=24143
View this thread: http://www.excelforum.com/showthread...hreadid=384021