Hi,

I have been constructing a spreadsheet and have the following formula which
subtracts 6 months from the cell in A1 =DATE(YEAR(a1),MONTH(a1)-6,DAY(a1)) -
this formula works fine.

If there is no date in A1, "#VALUE!" is displayed. is there an addition part
to the above forumla that would leave the cell empty unless A1 has data in it?

thanks,
Bryan

=IF(A1="","",DATE(YEAR(a1),MONTH(a1)-6,DAY(a1)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bryan" wrote in message
...
Hi,

I have been constructing a spreadsheet and have the following formula
which
subtracts 6 months from the cell in A1
=DATE(YEAR(a1),MONTH(a1)-6,DAY(a1)) -
this formula works fine.

If there is no date in A1, "#VALUE!" is displayed. is there an addition
part
to the above forumla that would leave the cell empty unless A1 has data in
it?

thanks,
Bryan

Hi Bryan,

=DATE(YEAR(a1),MONTH(a1)-6,DAY(a1)) -
this formula works fine.

If there is no date in A1, "#VALUE!" is displayed. is there an addition part
to the above forumla that would leave the cell empty unless A1 has data in it?


Su

=IF(A1="","",DATE(YEAR(a1),MONTH(a1)-6,DAY(a1)))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Bob/Jan Karel,

Thanks a lot - works perfectly!
Bryan

"Jan Karel Pieterse" wrote:

Hi Bryan,

=DATE(YEAR(a1),MONTH(a1)-6,DAY(a1)) -
this formula works fine.

If there is no date in A1, "#VALUE!" is displayed. is there an addition part
to the above forumla that would leave the cell empty unless A1 has data in it?


Su

=IF(A1="","",DATE(YEAR(a1),MONTH(a1)-6,DAY(a1)))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com