Use CASE or IF statement?

Recommended Posts

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!

Share on other sites

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.

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)

Share on other sites

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

Share on other sites

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

Share on other sites

Must be my turn for a good night

Share on other sites

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

Share on other sites

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.

Share on other sites

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.

Share on other sites

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.

Share on other sites

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

Share on other sites

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?

Share on other sites

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

Share on other sites

Take out the spcae between CURRENT and DATE

Steve

Share on other sites

Thanx Steve,

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

Now the above calc is your ANNIVERSARY,

Anniversarydate - Status(CurrentDate)

Share on other sites

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

Share on other sites

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?

Share on other sites

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

)

Share on other sites

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

Share on other sites

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.

Share on other sites

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.

Share on other sites

Lee,

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

Share on other sites

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.

Share on other sites

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

Share on other sites

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

Share on other sites

Lee,

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

Share on other sites

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.

Share on other sites

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

Share on other sites

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!

Share on other sites

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

Share on other sites

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

Share on other sites

Ugo,

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

Share on other sites

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

Share on other sites

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?

Share on other sites

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.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...