calculation of expiration date

**deth** · 04-14-2012, 04:47 PM

I am trying to create a policy monitoring worksheet wherein when i enter an initiating date say in column A2 the expiration date (which is every 2 years) will automatically enter in column C2. However, if a policy has just renewed, the initiating date will still be in A2 and the renewal date will be entered in column B2. Automatically the computer calculates the expiration date based on the date entered in column B2.

Can you help me with this.

**FDibbins** · 04-14-2012, 05:04 PM

try....

=if(B2="",A2+730,B2+730)

**rollis13** · 04-14-2012, 05:16 PM

Maybe something like this in C2:

=IF(B2="",DATE(YEAR(A2)+2,MONTH(A2),DAY(A2)),DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)))

**kevinsenjaya** · 04-14-2012, 07:23 PM

are you mean countdown time?

**deth** · 04-15-2012, 02:55 PM

Thank you, FDibbins, it worked on my worksheet. I have a new argument, however. Is it possible to format the whole column C2 with this formula? I tried it in the conditional formatting, but failed.

**FDibbins** · 04-15-2012, 03:19 PM

yes, its a simple formula, you should be able to just copy it down to wherever you need it?

If you want to add conditional formating (to change cel color for instance) let me know and we can work on that

**deth** · 04-15-2012, 03:48 PM

Thank you so much, FDibbins.

**FDibbins** · 04-15-2012, 05:14 PM

Glad to help, shout if you need anything else. It would be great if you could add to my reputation by clicking on the star below

**Haseeb Avarakkan** · 04-15-2012, 05:20 PM

So if you are using 730 what is the required output in leap years?

eg: If initial date is 1/1/2011 what is the required expiry date, 31-Dec-12 or 1-Jan-13? If the answer is 1-Jan-13 try with EDATE

=EDATE(IF(B2="",A2,B2),24)