Excel formula help

[Montanasvt]

I need some help from any of you spreed sheet experts out there. I am starting a new job and would like to keep a record of when to contact my customers. So my question. What formula could I use to represent the date I entered into the spreedsheet to show and the date I need to contact the customers 20 months from the day I enter? Pretty much. In one cell I will have the date the customer came to see me, then in the cell beside it I would like it to show me a new date that is 20 months later and possibly highlight itself the day that I need to contact them.

 

[DefCon3]

Use the edate function:

EDATE Returns the serial number of the date that is the indicated number of months before or after the start date

Example:

=edate(B4,20)

Assume the contact date is in cell B4, in date format. The following formula will display the "serial number" of a date 20 months later. Simply format that cell/column as Date format to show the date 20 months later.

 

[Teethy]

...And then pick a cell that you don't need and type =Today() and use conditional formatting to format the column that has the return call date to turn a cell red if the date for return call is in the past based on the current date cell. That formula will look something like this...

=IF(M2<$M$53,TRUE,FALSE)

M2 will be the cell being formatted. M53 is set absolute and is always referenced as todays date. The true condition will be whatever you set the format as. False can be formatted differently or not at all.

Hope this helps.

 

[Montanasvt]

thanks to both of you....great info and it works!