11 Replies Latest reply on May 4, 2012 12:41 PM by ian.moree

# Why do this calculation for day of week this way?

// return the date that is the Monday on or before the first day worked

Let (

[

first_worked_date = Date ( Month (TimeEntry::timeStart); Day ( TimeEntry::timeStart); Year (TimeEntry::timeStart));

// Sunday = 1

first_worked_daynum = DayOfWeek ( first_worked_date);

// how many days prior is the Monday?

offset = Choose ( first_worked_daynum; 0 ; -6; 0 ; -1; -2; -3; -4; -5)

];

first_worked_date + offset

)

eg / dayofWeek( "5/7/2012" ) = 2 or Tuesday; So

choose above's 2nd result is 0 ? which means we are on the correct day ?

if i continue.

if dayofWeek( "5/8/2012") = 3 0r Wednesday , we are 2 days away from sunday correct?

what am i missing here in this calculation PLEASE>.

thanks

-ian

• ###### 1. Re: Why do this calculation for day of week this way?

Is it one day off?  because 5/7/12 is actually a MONDAY and 5/8/12 is TUESDAY.

• ###### 2. Re: Why do this calculation for day of week this way?

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?

• ###### 3. Re: Why do this calculation for day of week this way?

Also Days are Sunday = 1 through Saturday = 7.

• ###### 4. Re: Why do this calculation for day of week this way?

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

-i

• ###### 5. Re: Why do this calculation for day of week this way?

Because you are a day off, SUNDAY = 1 (so -6 would be the previous Monday) MONDAY = 0, Tuesday = -1 etc.

• ###### 6. Re: Why do this calculation for day of week this way?

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.

• ###### 7. Re: Why do this calculation for day of week this way?

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.

• ###### 8. Re: Why do this calculation for day of week this way?

thats what i thought as well. Thnks digitalcarpentry...

-i

• ###### 9. Re: Why do this calculation for day of week this way?

because it is 0, then that would be equal to "THIS START WEEK!" , not previous week//

am i correct in this thinking?

-i

• ###### 10. Re: Why do this calculation for day of week this way?

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.

• ###### 11. Re: Why do this calculation for day of week this way?

thanks dc...

appreciate the feedback / help..