Use CASE or IF statement?

[KLA]

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!

[paulage77]

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)

[Ugo DI LUCA]

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 !?

[paulage77]

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

[Ugo DI LUCA]

Must be my turn for a good night

[Ugo DI LUCA]

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

[KLA]

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.

[Ugo DI LUCA]

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.

[KLA]

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.

[Ugo DI LUCA]

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

[KLA]

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?

[KLA]

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

[SteveB]

Take out the spcae between CURRENT and DATE

Steve

[Ugo DI LUCA]

Thanx Steve,

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

Now the above calc is your ANNIVERSARY,

Anniversarydate - Status(CurrentDate)

[Lee Smith]

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

[KLA]

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?

[KLA]

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

)

[Lee Smith]

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

[KLA]

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.

[Ugo DI LUCA]

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.

[Ugo DI LUCA]

Lee,

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

[KLA]

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.

[Lee Smith]

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

[Ugo DI LUCA]

see attached

kva.fp5.zip

[Ugo DI LUCA]

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

[Ugo DI LUCA]

Lee,

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

[Lee Smith]

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.

[KLA]

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

[KLA]

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!

[Ugo DI LUCA]

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

[Lee Smith]

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

[KLA]

Ugo,

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

[Lee Smith]

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

[KLA]

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?

[KLA]

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.