display date

[dmframe]

I am calculating exhaust of a piece of equipment and if
the exhaust is in the next 18 months I want to display the
exhaust date as mmm-yy. If however, the exhaust date is
beyond that I want to display the date as yyyy.
I've been trying to use a formula...
if(the exhaust date is less than (today plus 18 months),
then date, else YEAR(date))
Help please.
DMF

 

[Norman Harker]

Hi DMF!

Try:

=IF(A1<DATE(YEAR(TODAY())+1,MONTH(TODAY())+6,DAY(TODAY())),TEXT(A1,"mm
m-yy"),TEXT(A1,"yyyy"))

Note however, that the return of this formula is text and not a date.
If you need to use it for subsequent calculations you'll either have
to parse it or work on the base data in A1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 14th July: France, French Guinea,
French Polynesia, Guadeloupe, New Caledonia, St Martin, and St. Pierre
& Miquelon (Bastille Day); Martinique and Mayotte (National Days);
Madagascar (Fandoana Bathing Festival); Sweden (Crown Princess'
Birthday);Turkmenistan (Turkment Bakhsi Holiday); United Kingdom
(Emmeline Pankhurst Day). O-Bon / Festival of Souls (Shinto). NYT of
14-Jul-2003 reports Iraq National Day as cancelled public holiday.
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[DH]

What you are really saying is set one format based on a true condition and a
different format based on a false condition. As a number format you 'may' be
able to do it with conditonal formatting, but if you want an easy way, then
use an if statement

=if(a1<today()+18*30,text(a1,"mmmm-yy"),text(a1,"yyyy"))

dh