Basically I have two calculation fields

1) ANNIVERSARY DATE which =

Date(Month(Date of Hire), Day(Date of Hire), Year(Today's Date)). The problem here is that sometimes the YEAR of the anniversary date is not 2003 but 2002 (like if they started in Dec.) So, this doesn't quite work. So, I created another field ANDATE2 that is the same formula but with the YEAR as Today's Date-1. Both of these fields work fine but I had used the ANNIVERSARY DATE in another calculation field:

2) WEEKS =

Case(

IsEmpty(Today's Date) or IsEmpty(Anniversary Date), TextToNum(""),

Round(

((Today's Date - Anniversary Date))

/ 7

,0)

)

So, I have to change this field to reflect the ANDATE2 field as well. I'm having a problem in knowing how to add this in. Basically, I want this WEEKS field to say, if the YEAR = the YEAR of TODAY's DATE, then use ANNIVERSARY DATE in the above calculation, otherwise, use the ANDATE2 in the calculation. I'm sure I'm supposed to use CASE but not sure how to set it up. Or maybe there's another way to do the ANNIVERSAY DATE so that it relects the correct amount of weeks worked to date for the employee's current year. My last try looked like this:

Case(

IsEmpty(Today's Date) or IsEmpty(Anniversary Date) or IsEmpty(Anniversary Date2), TextToNum(""),

Case(

Year(Today's Date) = Year(Anniversary Date2), Round(((Today's Date - Anniversary Date2)/7),0),

If(

Year(Today's Date) = Year(Anniversary Date), Round(((Today's Date - Anniversary Date)/ 7),0),

0)

))

Probably don't need that ISEMPTY part, but I copied it from another calc. Those fields really wouldn't be empty anyway.

HELP!

it looks like the first part of the second case will always fail

(Year(Today's Date) = Year(Anniversary Date2) since Todays date will always be 2003.

How about:

Case(

IsEmpty(Today's Date) or IsEmpty(Anniversary Date) or IsEmpty(Anniversary Date2), TextToNum(""),

Case(

Year(Today's Date) > Year(Anniversary Date2), Round(((Today's Date - Anniversary Date2)/7),0),

Year(Today's Date) = Year(Anniversary Date), Round(((Today's Date - Anniversary Date)/ 7),0),

0)

Not sure I get it all....

You want to identify if the current week is the Anniversary date for first year of one selected employee ?

I think something like :

WeekAnniversaryDate = Case(Week(Status(CurrentDate) = Week(Date of Hire +365), "Anniversary", " ").

There sure is a Mod count for this, but I'm lazy right now.

Or am I missing something...

would be easier !?

Ugo - that is not complicated enough and it's entirely too easy to understand.

Must be my turn for a good night

OK,

So you want the Week field to be a counter of the weeks to the Anniversary date.

Anniversarydate = Date(Month(Date of Hire), Day(Date of Hire), Year(Date of Hire+1))

WeekAnniversarydate = WeekofYear(Anniversarydate)

Weekcountdown = WeekAnniversarydate- WeekofYear(Status(CurrentDate))

or in only one calc :

Weekcountdown= WeekofYear(Date(Month(Date of Hire), Day(Date of Hire), Year(Date of Hire+1)))-WeekofYear(Status(CurrentDate))

I was out of town.... Now then.... to clarify:

I need a field (which I called WEEKS) that calculates the number of days from the most recent ANNIVERSARY date to the current date. That is how many days worked in the current year of employement. So, I created the field ANNIVERSAYR DATE to use in the calculation of this formula, however, it seems that the calculation is incorrect because I used the year of the today's date, which it may not be, if the anniversary fell in December 2002, say. I don't think I can use ( Date of Hire +1) or (+365 days) because it's plus however many years or days it's been since the hire date.

Hi, I don't get it....

If Bill was employed on December 25th, his Anniversary date of employement would surely be December 25th of this current year.

Now, has you said you wanted his FIRST anniversary date, you may use

Anniversarydate = Date(Month(Date of Hire), Day(Date of Hire), Year(Date of Hire)+1))

where I modified the last part that was a typo problem.

No, if Bill's first day of work is December 20, 2001, then he is in his SECOND year of employment (Dec. 20,2001- Dec. 20, 2002 = First year) and I need to calculate how many days he has worked in this second year of employement. So, I need to calculate Today's date minus his most recent anniversary date - December 20, 2002.

So you want the Week field to be a counter of the weeks from the latest Anniversary date.

Anniversarydate = Case(Year(Date of Hire) = Year(Status(Current Date), Date(Month(Date of Hire), Day(Date of Hire), Year(Status(Current Date)), Date(Month(Date of Hire), Day(Date of Hire), Year(Status(Current Date)-1))

So, I should use that whole thing you just said as the calculation for the WEEKS field? What is the formula for the ANNIVERSARY field?

Also, it doesn't recognize CURRENT DATE as a STATUS flag. I'm using version 6.

Take out the spcae between CURRENT and DATE

Steve

Thanx Steve,

Must be bad translation again from French version to US...

Now the above calc is your ANNIVERSARY,

Anniversarydate - Status(CurrentDate)

Hi Seve,

I was getting to point that out also, but when I tested the calculation, I don't think that it gives the expected answer.

Hi KLA,

Try this calculation and see if it dosen't do for you.

Status(CurrentDate)-

Date(Month(Date of Hire), Day(Date of Hire), Year(Status(CurrentDate)))

HTH

Lee

Thanks. I'm trying to test that formula you just gave me but I'm having problems with having too many separators. Yours was happening at the first Year(status(currentdate) and I fixed that one but now mine stops at the second Year(status(current date). I posted my revision below:

Case(

Year(Date of Hire) = Year(Status(CurrentDate)),

Date(Month(Date of Hire), Day(Date of Hire), Year(Status(CurrentDate)), Date(Month(Date of Hire), Day(Date of Hire), Year(Status(Current Date)-1))

)

Am I missing something?

Thanks guys... I'm still trying to figure this out. LEE, I tried your calc and it doesn't give me the correct answer. If the start date is 5/8/01 - i'm getting 41 weeks instead of 6. And UGO, I'm getting "too many separators" with the commas in yours. I got stuck after the first Year(status(currentdate) and I rewrote it but now I get stuck after the second Year(status(currentdate). Here's what I have:

Case(

Year(Date of Hire) = Year(Status(CurrentDate)),

Date(Month(Date of Hire), Day(Date of Hire), Year(Status(CurrentDate)), Date(Month(Date of Hire), Day(Date of Hire), Year(Status(Current Date)-1))

)

KLA

That's the number of days, to get the number of weeks (6), use this modified calculation:

Round((Status(CurrentDate)-

Date(Month(Date of Hire), Day(Date of Hire), Year(Status(CurrentDate))))/7, 0)

Lee

Nope, still doesn't work! I have someone who started on Dec. 15, 1998. It should be counting the weeks since December 15, 2002. I'm getting a negative number (-26). It should be positive 26. It works fine for someone whose anniversary falls in 2003. Like someone starting 5/5/2001 because their anniversary date is 5/5/3.

Sorry for that...

Case(

Year(Date of Hire) = Year(Status(CurrentDate)),

Date(

Month(Date of Hire), Day(Date of Hire), Year(Status(CurrentDate))

)

, Date(

Month(Date of Hire), Day(Date of Hire), Year(Status(Current Date)-1))

)

You were missing one parenthesis.

Lee,

His field WEEK is in fact a Days Count down !!

UGO: your calculation gives me 731564. I need the number of weeks , which should be 26. Lee's worked except I was getting a negative number.

Hi Ugo,

I'm not sure what KLA really wants. Your original response seemed to answered the question, I thought.

Then I picked up that KLA wanted days, so I gave that a try days (albeit it broke when the month was in the future).

Which then KLA responded the need was for weeks.

Now I'm not so sure what KLA wants.

But here is one that at least takes care of the weeks. I tested it and it didn't seem to break.

Mod(WeekofYear(Date of Hire) - WeekofYear(Status(CurrentDate)) + 52, 52)

and here is one for days.

Mod(DayofYear(Date of Hire) - DayofYear(Status(CurrentDate)) + 365, 365)

HTH

lee

see attached

kva.fp5.zip

Fine Lee,

Now for the non 365 years !!

Mod(WeekofYear(Date of Hire) - WeekofYear(Status(CurrentDate)) + WeekofYear(Date(12,31,Year(Status(CurrentDate)), WeekofYear(Date(12,31,Year(Status(CurrentDate)))

and here is one for days.

Mod(DayofYear(Date of Hire) - DayofYear(Status(CurrentDate)) + DayofYear(Date(12,31,Year(Status(CurrentDate)), DayofYear(Date(12,31,Year(Status(CurrentDate)))

Lee,

Substitute(My last post, "fine", "AWESOME!!!!")

I liked it better (pardon the pun) after your modification.

In fact, I was going to write and tell you that , and to say "******* we're a good team" and Hopefully we finally gave KLA what he needed.

Ugo, I'm trying to test your weeks one:

Mod(WeekofYear(Date of Hire) - WeekofYear(Status(CurrentDate)) + WeekofYear(Date(12,31,Year(Status(CurrentDate)), WeekofYear(Date(12,31,Year(Status(CurrentDate)))

And i'm getting a separator problem. - after CurrentDate on 3rd line

By the way, I'm looking for weeks, not days. I've got other fields that result in days and might have gotten confused. For this one, I need weeks. Thank you guys for helping me! This is my last problem to figure out with this database... I hope!

Hi,

Actually, this was an extent to Lee's calc, which was :

Mod(WeekofYear(Date of Hire) - WeekofYear(Status(CurrentDate)) + 52, 52)

Now, in order to be sure that there is actually 52 weeks in that year, just substitute the 52 by :

WeekofYear(

Date(

12,31,

Year(Status(CurrentDate)

)

)

Again, another missing parenthesis.

So the calc is :

Mod(WeekofYear(Date of Hire) - WeekofYear(Status(CurrentDate)) + WeekofYear(

Date(

12,31,

Year(Status(CurrentDate)

)

), WeekofYear(

Date(

12,31,

Year(Status(CurrentDate)

)

)

)

or

Mod(WeekofYear(Date of Hire) - WeekofYear(Status(CurrentDate)) +WeekofYear(Date(12,31,Year(Status(CurrentDate))), WeekofYear(Date(12,31,Year(Status(CurrentDate))))

Sorry guys, I caught that and forgot to post the correction.

Ugo,

I copied and pasted and STILL got too many separators after the first 12,31,Year(Status(CurrentDate)))

Mod(WeekofYear(Date of Hire) - WeekofYear(Status(CurrentDate)) + WeekofYear(Date(12,31,Year(Status(CurrentDate)))), WeekofYear(Date(12,31,Year(Status(CurrentDate)))))

well, the formula has enough ((('s now, but it's giving me the wrong answer. It's giving me 47 instead of the correct answer, 6. That's if my Start date is 5/8/01. Which means my most recent anniversary date is 5/8/03. Which means it's been 6 weeks since then. This is giving us all a work out! Any more tries?

It does give the correct answer if my start date is Dec. 15, 1998. Then it's 26 and it's not negative anymore, so that's good.

Please make up your mind of what you want.

First you ask for a count down and now you want a count from.

Two entirely different calculations.

Lee

sorry, i'm not an expert at this. I need to caluclate TODAYS DATE - THE ANNIVERSARY OF THE PERSONS START DATE and have it return in weeks.

Example:

If they started on 5/8/01, then it would be 6/8/03(today) - 5/8/03 {= 6 weeks}

If they started on 12/8/98, then it would be 6/8/3 - 12/8/02 (since this is the most RECENT anniversary date) {= 26 weeks}

The purpose is to tell me how many weeks they have worked in their CURRENT year of employment so i can tabulate how many vacation days they have earned so far in their current year. Everyone's vacation days start over on their start date anniversary. Hope this is more clear.

KLA

Here is the reverse of the last one and should be what you want.

Mod(WeekofYear(Status(CurrentDate)) - WeekofYear(Date of Hire) +

WeekofYear(Date(12,31,Year(Status(CurrentDate)))), WeekofYear(Date(12,31,Year(Status(CurrentDate)))))

Lee

I THINK this works. I have to make sure it's what my boss wants but looks good! THANKS sooooo much for both of your help!!