Is it one day off? because 5/7/12 is actually a MONDAY and 5/8/12 is TUESDAY.
Are you just trying to find out if the DateField = Monday, and if not, how many days earlier was the previous Monday? [ DayOfWeek(Monday) = 2 ]
Test the DayofWeek for the date in question and, calculate a Case statement:
- if DayOfWeek = 2; date IS Monday (whatever you need to put here)
- Else if DayOfWeek > 2, DayOfWeek - 2 is days after latest Monday
- Else 2 - DayOfWeek, days before Monday.
What are you trying to do with the result of your calc?
Also Days are Sunday = 1 through Saturday = 7.
My thing is why the 0 , for Tuesday ?
offset = Choose ( first_worked_daynum; 0 ; -6; 0 ; -1; -2; -3; -4; -5)
See here ( 0 ; -6; 0 ; -1 ; -2; -3; -4 ; -5)
the 0 after -6, why a 0 here ..
monday = 1; -6
tuesday = 2 ; 0
wed = 3 ; -1
thur = 4 ; -2
fri = 5 ; -3
sat = 6 ; -4
sun = 7; -5
Because you are a day off, SUNDAY = 1 (so -6 would be the previous Monday) MONDAY = 0, Tuesday = -1 etc.
The Choose function starts with 0, thus yhr first choice is 0, Sun, Mon, Tue Wed, Thur, Fri, Sat or 0, -6, 0, -1, -2, -3, -4, -5
Check FM Help file to see how functions work.
Yes but it's the second 0 that he was asking about, that 0 represents results for Monday, it will subtract 0 days (because it's already Monday) if you wanted to return the previous Monday in that situation just change that 0 to -7. Where I was referring to him being one number off is in his day calculation where he showed Monday =1, Tuesday = 2, etc. He's a number off there, because actually Sunday = 1, Monday = 2, etc.
thats what i thought as well. Thnks digitalcarpentry...
because it is 0, then that would be equal to "THIS START WEEK!" , not previous week//
am i correct in this thinking?
Yes, if they start on Monday, it would give you That day as the Monday, NOT the Monday of the previous week. If you need it to roll back a week, just change that 0 to -7.
Personally I might change the first 0 to "ERROR" or something, you shouldn't ever get that as a result, but if you did, you would know you had an error rather than getting a date that is just a week off. I like to make errors obvious so I know there is a problem.
appreciate the feedback / help..