Year to day calculation

[CWSwarthout]

I know I can't see the forest for the trees, but I can't seem to find anywhere where someone has addressed the exact day, year to year, calculation. Part of our work is generating license badges that are supposed to expire on the same day, year to year. We caused much chaos in the d/b because we did the ol' date plus 365 and then changed the calculation to 366 to make up for the leap year. Computer records were then different from license badges already issued. If someone could email me at [email protected], it would be greatly appreciated. I will be too busy hiding in my cubicle to log back into the forum....thanks in advance!!!!

[-Queue-]

What about Date( Month( date1 ), Day( date1 ) + 365, Year( date1 ) )?

[CWSwarthout]

I just plugged in the formula, and using today (8/27/03) the result was 8/26/04, so it seems to be a day off. Unless I am doing something wrong. Thanks for the advice, I interested in what you think...

[-Queue-]

What is 2004?

[CWSwarthout]

It looks like to me it won't account for the leap year. As I plugged in '04 it was fine, '05, '06 all okay, but it doesn't work for '03 or '07.

[CWSwarthout]

Maybe I haven't been specific enough? We generate a "license approved date" and the powers that be want it to expire exactly one year later on the same date.

[-Queue-]

Perhaps I read your problem incorrectly. Do you want something that is exactly one year after the current date or is always the same month and day, with only the year changing? If it's the latter, then Date( Month( date1 ), Day( date1 ), Year( date1 ) + 1 ) is more suited to your needs.

[CWSwarthout]

Hey, that's the ticket! Accounts for the leap years, brings me the exact date, one year later, that is what I needed. Where can I now go to understand how this calculation works? Would like to understand the "why" and "how" of it. Again, thanks alot. This database tracks the licensing for 2,500 employees, and we have been doing it wrong for about five years! Big help!!

[-Queue-]

The explanation is fairly simple. Date() transforms a given month, day, and year into a usable date field. Adding one to the year portion of it, and not affecting either of the other options, adds one to the year of the referenced field. So 2003 becomes 2004, and nothing else within the date changes.

Effectively, it takes 08/27/03 and replaces the '03' with '04', ignoring the remaining month and day data.

Hope this is helpful for you.

[Lee Smith]

Hi Queue and CW,

If your "license approved date" happens to be on 2/29/2004, Queue's calculation will show expiration date as 3/1/2005, normal. However, if you want it to be 2/28/2005 than this one will do that.

If(Month(AnyDate) = 2 and Day(AnyDate) = 29,

Date(2,28,Year(AnyDate)+1),

Date(Month(AnyDate),Day(AnyDate),Year(AnyDate)+1)

)

HTH

Lee

[-Queue-]

Good catch, Lee. I haven't used anything like Year() + 1 before (usually the Day() + 365 is sufficient), so I wouldn't have thought about the '29'th issue.

[CWSwarthout]

My thought is since management didn't think about this when they made this change, either one would work. We used to just add the 365 and called it good, let the dates fall where they would.... We just decided among ourselves here to try to never let the "powers" approve any licenses on 2/29 of any leap year. Hey, this has been a great help, I sincerely thank both of you.

[-Queue-]

Duh. Forget about both mine and Lee's calcs.

Date( Month( date1 ) + 12, Day( date1 ), Year( date1 ) ) solves the problem more easily.

[Lee Smith]

Queue,

This one gave the same result as your first one using 2/29/2004 as date1.

Am I missing something here?

[-Queue-]

Okay, I want to delete my previous post now. I must be stoned, yet I'm not.

I'm confused though, rereading your original one. I can't figure out if it's more logical for 02/28 or 03/01 to be the result.

The inventors of leap year should be drug out into the street and shot, as Garfield would likely say.

And/or FileMaker should build a date function that takes it into account without having to work around it.

[CWSwarthout]

I thought it was a good solution, if there isn't a Feb. 29th only once every four years, what can you do?

[Lee Smith]

Hi Queue,

As I stated in my original post, your calculation was the normal result. I only introduce this one so that CW could give management an opportunity to make a decision. They need to do that once in awhile or they start to get restless. But, CW said something to the effect that management didn't even need to know there was a leap year every four years.:

What bothers me more about the calculation he wanted is that they license is issued for one year and a day.

Lee

[-Queue-]

Understood, I just felt a rant was in order since this isn't a built-in option for management to be able to make.

Woe unto thee when management is restless, for thine job mayest be in jeopardy, and they willst not accept answers in the form of questions.

[CobaltSky]

Actually, I think you can all relax, because in my experience 1st March is generally accepted as the date one year on from 29 February.

During a leap year, as a matter of course everyone gets an 'extra day's worth' for their annual subscriptions - an anomaly which passes unquestioned. Those unwitting individuals whose subscriptions happen to start on 29 Feb are not generally singled out for exemption from this blind generosity.

This is the rule as I've seen it applied in the allocation of passports, land grants, university fees, tax remittances, rentals, equipment hire, publishing equity and sundry other annual fee, licensing and subscription systems - so I'd hazard a guess it might apply here also...

[-Queue-]

Ray to the rescue! Thanks for sparing my sanity.

[Lee Smith]

CobaltSky said:

I think you can all relax

No sweat, already was before you responded.