Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Age & Time Restricted Validation dates ...
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 <atyahoo<dotcom ---- Kane wrote in message ... Copied to K37, & error message, the formula you typed contains an error. < =, -, or ( ) .... |
#8
|
|||
|
|||
Sample book on way over ..
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#9
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#10
|
|||
|
|||
"Kane" 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 <atyahoo<dotcom ---- |
#11
|
|||
|
|||
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" 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 <atyahoo<dotcom ---- |
#12
|
|||
|
|||
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" 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 <atyahoo<dotcom ---- |
#13
|
|||
|
|||
"Kane" 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 <atyahoo<dotcom ---- |
#14
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#15
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#16
|
|||
|
|||
"Kane" 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 <atyahoo<dotcom ---- |
#17
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#18
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#19
|
|||
|
|||
You're welcome, Wayne !
Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Kane 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Window? | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
Date and Time Macro | Excel Discussion (Misc queries) |