Date Puzzle

[faaslave]

I see that a lot of you are good at date calcs, this one is an interesting one.

I have a field for birthday, last medical, and next medical due.

Here is the problem. Currently I am using this for next medical due

Case (

Age < 40 ;

Date ( Month ( Last Medical) ; Day ( Last Medical ) ; Year ( Last Medical ) + 2) ;

Age ≥ 40;

Date ( Month ( Last Medical) ; Day ( Last Medical ) ; Year ( Last Medical ) + 1) ;

""

)

It works, but not exactly like I need.

1) I only want it to return the month and year the medical is due.

2) If a person has their last medical while still being 39 years old on the day of the medical (last medical), then their next medical is due in two years. Otherwise it is due every year, for us older guys.

Since they may turn 40 after their last medical, my logic will switch the next medical due up a year.

So how do you say, at the time of the last medical, get the age, and give me the result?

Seems like a fun one

Thanks Dave

[comment]

Assuming that Age is a calculation using DOB and Get(CurrentDate) as the input, you can compute the age at the time of the last medical in the same way - just use the same formula with LastMedical instead of Get(CurrentDate).

Regarding your first question, there's no such thing as a date without a day. So you can either extract the month and the year of the result as text, or format the field to show only the month and the year.

[faaslave]

Thank you for your reply. I do have one problem still.

These inputs will not be put in for at least a month after they take place.

Employee is 39 and has a medical. The next day he turns 40. His next medical is due in 2 years. Had he taken the medical one day later, his medical would be due in a year.

I will not be able to input this until a month has past, so current date would not work. I need the calc to just figure this out, if possible.

I will see if I can figure out how to format the field to omit the day.

my age calc is:

Let ( [

now = Get ( CurrentDate ) ;

DOBnow = Date ( Month ( Birthday) ; Day ( Birthday ) ; Year ( now ) ) 

] ;

Year (now ) - Year ( Birthday ) - ( now < DOBnow )

)

Thanks Dave

Edited September 13, 2006 by Guest

[faaslave]

Wouldn't something like this work, if I could figure out how to write it correctly?

Case (



Let ( [

now = Get ( Last Medical ) ;

DOBnow = Date ( Month ( Birthday) ; Day ( Birthday ) ; Year ( now ) ) 

] ;

Year (now ) - Year ( Birthday ) - ( now < DOBnow )



result?  < 40 ; 



Date ( Month ( Last Medical) ; Day ( Last Medical ) ; Year ( Last Medical ) + 2) ;





Let ( [

now = Get ( Last Medical ) ;

DOBnow = Date ( Month ( Birthday) ; Day ( Birthday ) ; Year ( now ) ) 

] ;

Year (now ) - Year ( Birthday ) - ( now < DOBnow )



result? ≥ 40;



Date ( Month ( Last Medical) ; Day ( Last Medical ) ; Year ( Last Medical ) + 1) ;



""



 )

That way it bases the age from the last medical date.

Dave

Edited September 13, 2006 by Guest

[comment]

Well, you could just duplicate your age calc, and replace:

now = Get ( CurrentDate ) ;

with:

now = Last Medical ;

Then use this copy in your next medical calculation, instead of the original Age. Of course, you could roll the two calcs together using Let().

To format the date, go to Layout mode, select the field, and select Format > Date.. from the menu.

[comment]

I see you are making some progress. I would make the whole thing a bit shorter:

Let ( [

lastAge = Year ( LastMedical ) - Year ( Birthday )

- ( LastMedical < Date ( Month ( Birthday ) ; Day ( Birthday ) ; Year ( LastMedical ) ) )

] ;

Date ( Month ( LastMedical ) ; Day ( LastMedical ) ; Year ( LastMedical ) + 1 + ( lastAge ≥ 40 ) )

)

[faaslave]

I see you are making some progress. I would make the whole thing a bit shorter:

Let ( [

lastAge = Year ( LastMedical ) - Year ( Birthday )

- ( LastMedical < Date ( Month ( Birthday ) ; Day ( Birthday ) ; Year ( LastMedical ) ) )

] ;

Date ( Month ( LastMedical ) ; Day ( LastMedical ) ; Year ( LastMedical ) + 1 + ( lastAge ≥ 40 ) )

)

That looks great, but how do I keep it going for the next scenario. < 40 +1 , ≥ 40 +2?

I get confused when using nested functions.

Thank you very much

Dave

[comment]

It's already built in. Just take your time to analyze it (and keep in mind that true evaluates to 1 and false evaluates to 0).

[faaslave]

It is brilliant!!!!

I am still trying to figure it out, but I must say it is amazing.

I kneel before Zod. LOL

Thanks,

Dave

[faaslave]

I just thought of another scenario.

Lets say that your medical is due Feb, 2006.

You can't can't get one until March 2006.

Now your last medical is 3/2006.

Your next medical is still due in your birth month 1 or 2 years later.

So for people who get there mediclas done late, the new due date is not calculated correctly.

What I ended up doing is changing the calc you made to field (next medical due calc), which is not displayed.

Then in the next medical due field, I put:

Date ( Month ( Birthday ) ; Day ( Next Medical Due Calc ) ; Year ( Next Medical Due Calc ) )

This ensures that the due date will come out correctly.

Thanks

Dave :

Edited September 13, 2006 by Guest

[comment]

You should check first, before you lavish praises. I reversed the age test by mistake - the last line should be:

...

] ;

Date ( Month ( LastMedical ) ; Day ( LastMedical ) ; Year ( LastMedical ) + 1 + ( lastAge [color:red]< 40 ) )

)

I am not sure I follow your new scenario: if someone missed their medical, say by a whole year - you're saying that their next medical needs to be a year earlier (if they are over 40, that would be right now)?

[faaslave]

You should check first, before you lavish praises. I reversed the age test by mistake - the last line should be:

...

] ;

Date ( Month ( LastMedical ) ; Day ( LastMedical ) ; Year ( LastMedical ) + 1 + ( lastAge [color:red]< 40 ) )

)

I am not sure I follow your new scenario: if someone missed their medical, say by a whole year - you're saying that their next medical needs to be a year earlier (if they are over 40, that would be right now)?

No, the praise is deserved. I probably didn't make myself clear earlier, so I did make one change. You told me to take my time and anylize what you wrote. I did as you said, (took me awhile) , then I figured out what you were doing, I changed it to:

Date ( Month ( LastMedical ) ; Day ( LastMedical ) ; Year ( LastMedical )[color:red] + 2 - ( lastAge ≥ 40 ) )

)

On the day of your medical, if you are 40, then every medical from here on out is every year.

The other scenario is this:

mediclas are always due in your birth month. Lets say yours is March. If for some reason you don't get your medical until April 1st, (sometimes happens), then under my logic you would be due for a medical in April next time. It would need to be March.

That is why I made the last addition I mentioned above.

I am actually working on a report, so the sceduler will get a report of who are due medicals in the upcoming month. I made it work, but I know it is pretty silly how I got it to work. I would never show how out of embarresment.

Keep your praise, it works great!

Dave