Date forumla

B

Bryan

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Bryan said:
Hi,

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

Jan Karel Pieterse

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?
Click to expand...

Sure:

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

Regards,

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