KLA Posted June 13, 2003 Share Posted June 13, 2003 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! Link to comment Share on other sites More sharing options...
paulage77 Posted June 13, 2003 Share Posted June 13, 2003 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) Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 13, 2003 Share Posted June 13, 2003 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 !? Link to comment Share on other sites More sharing options...
paulage77 Posted June 14, 2003 Share Posted June 14, 2003 Ugo - that is not complicated enough and it's entirely too easy to understand. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 14, 2003 Share Posted June 14, 2003 Must be my turn for a good night Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 14, 2003 Share Posted June 14, 2003 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)) Link to comment Share on other sites More sharing options...
KLA Posted June 18, 2003 Author Share Posted June 18, 2003 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. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 18, 2003 Share Posted June 18, 2003 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. Link to comment Share on other sites More sharing options...
KLA Posted June 18, 2003 Author Share Posted June 18, 2003 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. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 18, 2003 Share Posted June 18, 2003 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)) Link to comment Share on other sites More sharing options...
KLA Posted June 18, 2003 Author Share Posted June 18, 2003 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? Link to comment Share on other sites More sharing options...
KLA Posted June 18, 2003 Author Share Posted June 18, 2003 Also, it doesn't recognize CURRENT DATE as a STATUS flag. I'm using version 6. Link to comment Share on other sites More sharing options...
SteveB Posted June 18, 2003 Share Posted June 18, 2003 Take out the spcae between CURRENT and DATE Steve Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 18, 2003 Share Posted June 18, 2003 Thanx Steve, Must be bad translation again from French version to US... Now the above calc is your ANNIVERSARY, Anniversarydate - Status(CurrentDate) Link to comment Share on other sites More sharing options...
Lee Smith Posted June 18, 2003 Share Posted June 18, 2003 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 Link to comment Share on other sites More sharing options...
KLA Posted June 18, 2003 Author Share Posted June 18, 2003 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? Link to comment Share on other sites More sharing options...
KLA Posted June 18, 2003 Author Share Posted June 18, 2003 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)) ) Link to comment Share on other sites More sharing options...
Lee Smith Posted June 18, 2003 Share Posted June 18, 2003 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 Link to comment Share on other sites More sharing options...
KLA Posted June 18, 2003 Author Share Posted June 18, 2003 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. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 18, 2003 Share Posted June 18, 2003 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. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 18, 2003 Share Posted June 18, 2003 Lee, His field WEEK is in fact a Days Count down !! Link to comment Share on other sites More sharing options...
KLA Posted June 18, 2003 Author Share Posted June 18, 2003 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. Link to comment Share on other sites More sharing options...
Lee Smith Posted June 18, 2003 Share Posted June 18, 2003 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 Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 18, 2003 Share Posted June 18, 2003 see attached kva.fp5.zip Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 18, 2003 Share Posted June 18, 2003 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))) Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 19, 2003 Share Posted June 19, 2003 Lee, Substitute(My last post, "fine", "AWESOME!!!!") Link to comment Share on other sites More sharing options...
Lee Smith Posted June 19, 2003 Share Posted June 19, 2003 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. Link to comment Share on other sites More sharing options...
KLA Posted June 19, 2003 Author Share Posted June 19, 2003 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 Link to comment Share on other sites More sharing options...
KLA Posted June 19, 2003 Author Share Posted June 19, 2003 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! Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 19, 2003 Share Posted June 19, 2003 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)))) Link to comment Share on other sites More sharing options...
Lee Smith Posted June 19, 2003 Share Posted June 19, 2003 Sorry guys, I caught that and forgot to post the correction. Link to comment Share on other sites More sharing options...
KLA Posted June 19, 2003 Author Share Posted June 19, 2003 Ugo, I copied and pasted and STILL got too many separators after the first 12,31,Year(Status(CurrentDate))) Link to comment Share on other sites More sharing options...
Lee Smith Posted June 19, 2003 Share Posted June 19, 2003 Mod(WeekofYear(Date of Hire) - WeekofYear(Status(CurrentDate)) + WeekofYear(Date(12,31,Year(Status(CurrentDate)))), WeekofYear(Date(12,31,Year(Status(CurrentDate))))) Link to comment Share on other sites More sharing options...
KLA Posted June 19, 2003 Author Share Posted June 19, 2003 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? Link to comment Share on other sites More sharing options...
KLA Posted June 19, 2003 Author Share Posted June 19, 2003 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7608 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now