Age & Time Restricted Validation dates ...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Birthdate in A43
Issue Date in G37
New Valid to Date ( ? ) in K37 Restrictions are:
Age<40=12 months (to the 1st day of the following month)
Age>40=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 Age>40
Beyond Valid to date="Expired"
Click to expand...
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.
 
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
 
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
Click to expand...
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
 
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+365),MONTH(G37+365
)+1,1),DATE(YEAR(G37+365/2),MONTH(G37+365/2)+1,1)),IF(AND(G37>TODAY()+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>)
 
Copied to K37, & error message, the formula you typed contains an error. <
=, -, or ( ) >....
 
Think you were probably hit by a couple of inevitable line wraps / breaks
when you copy > pasted the formula from the post. The formula was ok in the
test book over here (No error messages).

I'll send you a sample book via private email with the formula implemented
for you to test out.
 
Success! It works.
Thanks

Another step to the equation....
Issue Date >G36
Due Date >K36
Click to expand...
Certificate is valid to the 1st day of the 25th month of the Issue Date
If renewed within 90 day grace period prior to expirey/Due date, ADD 24 months to the original Issue date.
If beyond the Due Date,"Expired"
Click to expand...
I would like both a 2month conditional format "Red" font warning, and
"Red"font for "Expired"... is this possible?

.... Wayne
 
Kane said:
Success! It works.
Thanks

Another step to the equation....
Issue Date >G36
Due Date >K36
Click to expand...
Certificate is valid to the
1st day of the 25th month of the Issue Date
If renewed within 90 day grace period
prior to expirey/Due date,
ADD 24 months to the original Issue date.
If beyond the Due Date,"Expired"
Click to expand...

Try in K36:

=IF(G36="","",IF(G36<TODAY()-90,"Expired",IF(AND(G36<TODAY(),G36>=TODAY()-90
),DATE(YEAR(G36+365*2),MONTH(G36+365*2)+1,1),"Not due yet")))

(above formula is all in one line,
you'd need to restore the line breaks/wraps
after copy > pasting into the formula bar)
I would like both a 2 month conditional format
"Red" font warning, and
"Red"font for "Expired"... is this possible?
... Wayne
Click to expand...

Try this

Select G36
Click Format > Conditional Formatting
Make the settings under Condition 1 as:
Formula Is | =G36<=TODAY()-60
Click Format button > Font tab > Red & bold > OK
Click OK at the main dialog

Repeat steps for K36, except change:
Formula Is | =K36="Expired"

--

These are some test sample dates in G36
and results in K36 with the constructs above effected
(dates in format: mm-dd-yy)

If in G36: 09/20/04 (date will be in red and bolded)
K36 will show: Expired (in red and bolded)

If in G36: 10/20/04 (date will be in red and bolded)
K36 will show: 11/01/06 (no font formatting - normal)

If in G36: 11/20/04 (no font formatting - normal)
K36 will show: 12/01/06 (no font formatting - normal)

If in G36: 12/20/04 (no font formatting - normal)
K36 will show: 01/01/07 (no font formatting - normal)

If in G36: 01/20/05 (no font formatting - normal)
K36 will show: Not due yet (no font formatting - normal)
 
I will input input the below info and see how it goes.

I was perhaps too quick on the responce to the previous problem, ie: Age &
Time Restricted ...
It appears that if I have a issue date prior to the 90 day period it returns
"Expired", when it should really start over from that new date. It might be a
good idea to delete the Expired prior to the 90 day, and just use within the
90 day period and the >90d=Expired.. The results would be the same.
therefore;
=<90days prior to expirery/Due date, to the 1st day or the 13th month following the previous Due date.
Click to expand...

In your sample file, I entered
A43 01/03/1951
G37 04/08/2004
K37 >your formula< = Expired... should read 03/01/2005
Click to expand...

...... Wayne
 
Tried the K36 event....
Again it seems to show a problem with Expired in relationship to >90day prior.
I also need to see the Due Date, and not "Not Due Yet"

I will send you back your sheet with my data in it for reference..

I really do appreciate your help. I would like to be able to come up with a
solution to these two items, and your are really helping.

..... Wayne
 
Kane said:
In your sample file, I entered ....
Tried the K36 event....
Again it seems to show a problem with Expired in relationship to >90day prior.
I also need to see the Due Date, and not "Not Due Yet"
Click to expand...

Need some clarifications from you:

a. The earlier sample date you quoted for G37 was *20-Oct-2004*, not
2-Oct-2004.

If you change the dates in both G37 and in G36 (in the file you returned to
me) to: *20-Oct-2004* (from 2-Oct-2004), you'll *get* the desired results of
1-May-2005 (in K37) and 1-Nov-2005 (in K36).

As the date: 2-Oct-2004 is already more than 90 days from today, so the
correct value of "Expired" *should* appear, yes? Or, have I been hopelessly
reading things the other way around ?

b. The C.F. suggested for cell G36 was implemented wrongly onto cell K37??
It was suggested to be done on *G36*, not K37

Pl check and clarify.

Also, pl provide some sample date inputs for G36 and G37 and the expected
results to be returned in K36 and K37. The sample dates proposed should
preferably test the entire time-line and invoke the entire lot of various
expected returns

Describe it along these lines (as per my last post):
.....
If in G36: 09/20/04 (date will be in red and bolded)
K36 will show: Expired (in red and bolded)
.....
If in G36: 10/20/04 (date will be in red and bolded)
K36 will show: 11/01/06 (no font formatting - normal)

etc
 
I just spent over an hour doing what I am doing now... lost it somehow?!
Here goes
....
All Dates in MM/DD/YY, Cell Format: > Date > March 14, 1998
....
I will forward a copy of the test sheet again, with notes
....
B-Date
A43: (a) 1964 ... =>40years Old, 6month validation period ;(b) 1965 <40years
old, 12month validation period ...
....
IFR
G36: 24month validation period; to the 1st day of the 25th month of Issue Date
....
Med
G37: (a) 6month validation period; to the 1st day of the 7th month of Issue
Date (b) 12month validation period: to the 1st day of the 13th month of
Issue Date
....
IFR
K36: 24month validation period; to the 1st day of the 25th month of Issue Date
....
K36: If beyond Due Date,"EXPIRED"; C.F. Red Bold font
....
K36: If renewed "within" 90 days of Due Date, ADD 24month to Due Date that
was in effect.
....
C.F. -2month Prior warning of Due Date, Red Bold font.
....
k37:(1)
(a) ,40, to 1st day of the 13th month of Issue Date; (b) =>40, to 1st day
of the 13th month of Issue Date.
....
K37: (2)
If beyond Due Date,"EXPIRED", C.F. RED Bold font
....
K37 (3)
-2 month Prior warning of Due Date, C.F. Red bold font
....
K37 (4)
If renewed "within" 90 days of Due Date
(a) <40yr ... ADD 12 month to current Due Date
(b) =>40 ... ADD 6 month to current Due Date
....
K37 (5)
If renewed PRIOR to 90 days of Due Date;
(a) age<40yr
(b) age=>40yr
.... same as condition K37 (1)
....
If in A43: 08/08/1965
A43 reads August 8, 1965 ( <40, 12 month validation )
....
If in G37: 07/14/04 (July 14, 2004)
K37 should show: 04/01/05 (February 01, 2005)
C.F. Red bold font ( 2 month warning )
....
If in G37: 08/24/04 ( August 24. 2004 )
K37 should show: 03/01/05 ( March 01, 2005 )
C.F. Red Bold font ( 2month warning )
....
If in G37: 09/15/04 ( September 15, 2004 )
K37 SHolld show: 04"01/05 ( April 01, 2005 )
C.F. N/A
....
If in G36: 01/01/2005 ( January 01, 2005 )
K36: should indicate: 02/01/07 ( February 01, 2007 ) (2yr)
....
If K36 is beyond Due Date, C.F. Red Bold font
....
If K36 is -2month of Due Date, warning form C.F. Red Bold font
....
If K36 is renewed "within" 90 day of Due Date, ADD 24month to Due Date that
was in effect.
....
K36: C.F. -2month warning of Due Date, Red Bold font
....
If in K36: 03/01/05 ( March 01, 2005 ), If in G36: 01/14/03 ( January 14,
2003 )
& enter G36: 01/03/05 ( January 03, 2005 ) ("within" 90 days of Due Date)
K36: should read 03/01/05 ( March 01, 2005 ) ( Add 24 mo to previous K37 Due
Date )
....
If in K36: 03/01/05 ( January 01, 2005 ); "EXPIRED"
....

If in G37: 01/02/05 ( Jan 02, 2005 )
K37 should show: 08/01/05 ( August 01,2005 )
....

Hope this all makes sense.

Wayne
 
Kane said:
I just spent over an hour doing what I am doing now.
Click to expand...

... spent a couple of hours here
trying to recap / figure out
where it all went awry ..
.. lost it somehow?! Here goes ...
Click to expand...

... maybe .. hopefully not
... no more hair on scalp to pull out said:
will forward a copy of the test sheet ...
Click to expand...

ok, just received, thanks
... it's only 6.50 am here ..
 
File with expanded layout and revised formulas sent over ..
See whether what I've done makes sense to you
Let me know.
 
Max ... just to update..all is a success up to now. I have modified a few
things and the world has not stopped spinning!

Thanks for the help.

Rgds ...Wayne
 
Back
Top