Date forumla

[Guest]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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)) -

 

[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?

Sure:

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

Regards,

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

 

[Guest]

Bob/Jan Karel,

Thanks a lot - works perfectly!
Bryan