[SOLVED] Age & Time Restricted Validation dates ...

Kane · 01-01-2005, 01:06 PM

>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"
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

Kane · 01-01-2005, 05:06 PM

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 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"
> 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 · 01-03-2005, 01:06 AM

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 <at>yahoo<dot>com
----
"Kane" <[email protected]> wrote in message
news:[email protected]...
> 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 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"
> > 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
> >
> >

Kane · 01-03-2005, 03:06 AM

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 <at>yahoo<dot>com
> ----
> "Kane" <[email protected]> wrote in message
> news:[email protected]...
> > 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 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"
> > > 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 · 01-03-2005, 04:06 AM

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>)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Kane" <[email protected]> wrote in message
news:[email protected]...
> 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

Kane · 01-03-2005, 10:06 AM

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+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>)
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Kane" <[email protected]> wrote in message
> news:[email protected]...
> > 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 · 01-03-2005, 11:06 AM

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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Kane <[email protected]> wrote in message
news:[email protected]...
> Copied to K37, & error message, the formula you typed contains an error.
<
> =, -, or ( ) >....

Max · 01-03-2005, 11:06 AM

Sample book on way over ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Kane · 01-03-2005, 06:06 PM

Success! It works.
Thanks

Another step to the equation....
>Issue Date >G36
>Due Date >K36

>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"
I would like both a 2month conditional format "Red" font warning, and
"Red"font for "Expired"... is this possible?

.... Wayne

"Max" wrote:

> Sample book on way over ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

Max · 01-04-2005, 12:06 AM

"Kane" <[email protected]> wrote
> Success! It works.
> Thanks

whew, glad it worked <g>
You're welcome !

> Another step to the equation....
> Issue Date >G36
> Due Date >K36

> 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"

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

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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Kane · 01-04-2005, 01:06 AM

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 date, no calculation
>=<90days prior to expirery/Due date, to the 1st day or the 13th month following the previous Due date.

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

...... Wayne

> >Due Date=Expired

"Max" wrote:

> "Kane" <[email protected]> wrote
> > Success! It works.
> > Thanks
>
> whew, glad it worked <g>
> You're welcome !
>
> > Another step to the equation....
> > Issue Date >G36
> > Due Date >K36
>
> > 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"
>
> 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
>
> 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)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

Kane · 01-04-2005, 02:07 AM

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

"Max" wrote:

> "Kane" <[email protected]> wrote
> > Success! It works.
> > Thanks
>
> whew, glad it worked <g>
> You're welcome !
>
> > Another step to the equation....
> > Issue Date >G36
> > Due Date >K36
>
> > 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"
>
> 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
>
> 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)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

Max · 01-04-2005, 05:06 AM

"Kane" <[email protected]> wrote
....
> In your sample file, I entered
> >A43 01/03/1951
> >G37 04/08/2004
> >K37 >your formula< = Expired... should read 03/01/2005
....
> 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"

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max · 01-04-2005, 05:06 AM

Typo in line:
> 1-May-2005 (in K37) and 1-Nov-2005 (in K36).

It should read as:
> 1-May-2005 (in K37) and 1-Nov-2006 (in K36).

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Kane · 01-04-2005, 07:06 PM

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

"Max" wrote:

> Typo in line:
> > 1-May-2005 (in K37) and 1-Nov-2005 (in K36).
>
> It should read as:
> > 1-May-2005 (in K37) and 1-Nov-2006 (in K36).
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

Max · 01-04-2005, 07:06 PM

"Kane" <[email protected]> wrote
> I just spent over an hour doing what I am doing now.

... spent a couple of hours here
trying to recap / figure out
where it all went awry ..

> .. lost it somehow?! Here goes ...

... maybe .. hopefully not
... no more hair on scalp to pull out <g>

> will forward a copy of the test sheet ...

ok, just received, thanks
... it's only 6.50 am here ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max · 01-05-2005, 01:06 AM

File with expanded layout and revised formulas sent over ..
See whether what I've done makes sense to you
Let me know.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Kane · 01-08-2005, 11:06 AM

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

"Max" wrote:

> File with expanded layout and revised formulas sent over ..
> See whether what I've done makes sense to you
> Let me know.
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

Max · 01-08-2005, 01:06 PM

You're welcome, Wayne !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Kane <[email protected]> wrote
> 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

[SOLVED] Age & Time Restricted Validation dates ...

LinkBack

Thread Tools

Rate This Thread

Display