Status of the certificates

Closed
ganesh - Jun 24, 2016 at 12:27 AM
Hello,

i am raja, i have no.of certificates with issue date ,expiry date, last annual survey & next annual range. i attached sample sheet below .I want a VBA code or excel formula to see my certificates status as below conditions:

https://drive.google.com/file/d/0B0qW4LOZ_aqyUEdNT1M5SWFqMFk/view?usp=sharing

A) if the certificate having issue date & expiry date, the status should show based on the expiry date compare with today date.below are the conditions

1) if the expiry date having is: N/A(life long validity), it should be shows the status as"Definitive" with green ink.

2)if the expiry date is less than today date-it should be shows the status"expired" with red ink(or the entire row will show with red text)

3) if the expiry date is less than 3 months from today date- it should be shows the status " ready to expire in --months (or) days" with yellow ink.

4)if the expiry date is greater than 3 months from today date- it should be shows the status " Definitive" with green ink.

Note: The columns of next annual range( from& to ) will show "N/A" automatically by above conditions.

B) if the certificate having issue date , expiry date & also last annual survey, it should be calculate the next annual range (from, to)& also shows the status based on the from & to columns compare with today date.below are the conditions.

From = Issue date(DD,MMM,YYYY) + 1 year - 3 months
To = Issue date(DD,MMM,YYYY)+ 1 year + 3 months

if i surveyed the certificate,

From = Issue date(DD,MMM,renewal year) + 1 year - 3 months
To = Issue date(DD,MMM,renewal year)+ 1 year + 3 months

i hope you understand my idea.


Regards,
Raja