Birthdate in A43
Issue Date in G37
New Valid to Date ( ? ) in K37
Restrictions a
Age<40=12 months (to the 1st day of the following month)
Age40=6 months (to the 1st day of the following month)
Revalidation grace period=90 days prior to Valid to Date=normal Valid to Date
Revalidation prior to 90 day grace period=new Age <40 or Age40
Beyond Valid to date="Expired"
How would I set this up? There is a continuation to this, but thought we
could start from here.
Happy New Year , & in Rememberance!

.... Wayne

THis is a certificate that must be renewed every 12 months, or 6 months
depending upon age; to maintain a licence.

"Kane" wrote:

Birthdate in A43
Issue Date in G37
New Valid to Date ( ? ) in K37
Restrictions a
Age<40=12 months (to the 1st day of the following month)
Age40=6 months (to the 1st day of the following month)
Revalidation grace period=90 days prior to Valid to Date=normal Valid to Date
Revalidation prior to 90 day grace period=new Age <40 or Age40
Beyond Valid to date="Expired"
How would I set this up? There is a continuation to this, but thought we
could start from here.
Happy New Year , & in Rememberance!

... Wayne

Not sure, but perhaps something along these lines
would be a start ...:

Try in K37:

=IF(OR(A43="",G37=""),"",IF(G37<TODAY(),"Expired", IF(G37<TODAY()+90,IF(DATED
IF(A43,G37,"y")<40,G37+365,G37+365/2))))

Format K37 as date
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kane" wrote in message
...
THis is a certificate that must be renewed every 12 months, or 6 months
depending upon age; to maintain a licence.

"Kane" wrote:

Birthdate in A43
Issue Date in G37
New Valid to Date ( ? ) in K37
Restrictions a
Age<40=12 months (to the 1st day of the following month)
Age40=6 months (to the 1st day of the following month)
Revalidation grace period=90 days prior to Valid to Date=normal Valid
to Date
Revalidation prior to 90 day grace period=new Age <40 or Age40
Beyond Valid to date="Expired"
How would I set this up? There is a continuation to this, but thought we
could start from here.
Happy New Year , & in Rememberance!

... Wayne

Max
I copied the formula as given, cells as stated, and I get "Expired" for all
dates.
Example would be (m/d/y)
Birthdate A43 .... 08/08/1960
Issue date G37 .... 10/20/2004
This should give me a new Due date of 05/01/2005
If the certificate is renewed within 90 days of the due date, the new Due
date is extended to the 1st day of the 13th month of the previous Due date.
I could send you a sample of what I am looking for if that would help

..... Tks Wayne

"Max" wrote:

Not sure, but perhaps something along these lines
would be a start ...:

Try in K37:

=IF(OR(A43="",G37=""),"",IF(G37<TODAY(),"Expired", IF(G37<TODAY()+90,IF(DATED
IF(A43,G37,"y")<40,G37+365,G37+365/2))))

Format K37 as date
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kane" wrote in message
...
THis is a certificate that must be renewed every 12 months, or 6 months
depending upon age; to maintain a licence.

"Kane" wrote:

Birthdate in A43
Issue Date in G37
New Valid to Date ( ? ) in K37
Restrictions a
Age<40=12 months (to the 1st day of the following month)
Age40=6 months (to the 1st day of the following month)
Revalidation grace period=90 days prior to Valid to Date=normal Valid
to Date
Revalidation prior to 90 day grace period=new Age <40 or Age40
Beyond Valid to date="Expired"
How would I set this up? There is a continuation to this, but thought we
could start from here.
Happy New Year , & in Rememberance!

... Wayne

Perhaps try this revised formula in K37 first:

=IF(OR(A43="",G37=""),"",IF(G37<TODAY()-90,"Expired",IF(AND(G37<TODAY(),G37
=TODAY()-90),IF(DATEDIF(A43,TODAY(),"y")<40,DATE(YEAR(G37+3 65),MONTH(G37+365
)+1,1),DATE(YEAR(G37+365/2),MONTH(G37+365/2)+1,1)),IF(AND(G37TODAY()+90,DAT
EDIF(A43,G37,"y")<40),DATE(YEAR(G37+365),MONTH(G37 +365)+1,1),DATE(YEAR(G37+3
65/2),MONTH(G37+365/2)+1,1)))))

Test it out with a couple of sample values in A43 and G37
(or just copy down, if you have corresponding values down from A43 and G37),
and see whether all the returns match the expected results .. (think the
returns should be closer now <g)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kane" wrote in message
...
Max
I copied the formula as given, cells as stated, and I get "Expired" for
all
dates.
Example would be (m/d/y)
Birthdate A43 .... 08/08/1960
Issue date G37 .... 10/20/2004
This should give me a new Due date of 05/01/2005
If the certificate is renewed within 90 days of the due date, the new Due
date is extended to the 1st day of the 13th month of the previous Due
date.
I could send you a sample of what I am looking for if that would help

.... Tks Wayne

Copied to K37, & error message, the formula you typed contains an error. <
=, -, or ( ) ....


"Max" wrote:

Perhaps try this revised formula in K37 first:

=IF(OR(A43="",G37=""),"",IF(G37<TODAY()-90,"Expired",IF(AND(G37<TODAY(),G37
=TODAY()-90),IF(DATEDIF(A43,TODAY(),"y")<40,DATE(YEAR(G37+3 65),MONTH(G37+365
)+1,1),DATE(YEAR(G37+365/2),MONTH(G37+365/2)+1,1)),IF(AND(G37TODAY()+90,DAT
EDIF(A43,G37,"y")<40),DATE(YEAR(G37+365),MONTH(G37 +365)+1,1),DATE(YEAR(G37+3
65/2),MONTH(G37+365/2)+1,1)))))

Test it out with a couple of sample values in A43 and G37
(or just copy down, if you have corresponding values down from A43 and G37),
and see whether all the returns match the expected results .. (think the
returns should be closer now <g)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kane" wrote in message
...
Max
I copied the formula as given, cells as stated, and I get "Expired" for
all
dates.
Example would be (m/d/y)
Birthdate A43 .... 08/08/1960
Issue date G37 .... 10/20/2004
This should give me a new Due date of 05/01/2005
If the certificate is renewed within 90 days of the due date, the new Due
date is extended to the 1st day of the 13th month of the previous Due
date.
I could send you a sample of what I am looking for if that would help

.... Tks Wayne