11 Replies Latest reply on Jul 28, 2016 2:49 PM by David Moyer

# What is the most effective way for capturing the current week (mon-sun).

Hello Developers,

What is the most effective way for capturing the current week (mon-sun).

I have a date field called “Meeting_Date”.  If a record gets modified and the “Meeting_Date” falls during the current week, I want to trigger a script.  How would you script and/or calculate this?  Thank you for any insight.

Alex

• ###### 1. Re: What is the most effective way for capturing the current week (mon-sun).

You need the DayOfWeek() function.  It returns 1 through 7, 1 being Sunday.

You'll need some logic to make it Monday through Sunday.

Edit:

My bad - you need WeekOfYear

• ###### 2. Re: What is the most effective way for capturing the current week (mon-sun).

But the DayOfWeek does not capture the current week.  Would I have to use that function and nested it with the current date function ?

• ###### 3. Re: What is the most effective way for capturing the current week (mon-sun).

perhaps this function may help. you can specify starting date, so Monday could be first day of week.

Test to see if this helps you in any way. Read the entire description and test for you needs. And some of the other Date Functions may help.

beverly

• ###### 4. Re: What is the most effective way for capturing the current week (mon-sun).

Get ( CurrentDate ) returns the current date

DayOfWeek( date ) returns a value of 1 thru 7 with Sunday = 1 and Saturday = 7.

The fact that you want a 'week' to run from Monday to Sunday complicates this a bit but the above functions can be combined to produce the date for the first and last days of the current week--which I think is what you wanted here?

A simple to follow method using Choose (Yes, you can also set up a calculation that adds/subtracts from the current date)

Let ( [ T = Get ( CurrentDate ) ;

dw = DayOfWeek ( T ) ;

Mon = Choose ( "" ; T - 6 ; T ; T - 1 ; T - 2 ; T - 3 ; T - 4 ; T - 5 ) ;

Sun = Mon + 6 ] ;

Mon & "... " & Sun

)

I've set this up to produce a text result of the Monday Date ... Sunday Date for the current week, but you can alter the last expression any number of ways depending on what you need.

Edit: My tired brain incorrectly remembered a function name. I've now corrected that error.

• ###### 5. Re: What is the most effective way for capturing the current week (mon-sun).

Thank you all for the suggestions....going to run with these ideas and post my result later today.

• ###### 6. Re: What is the most effective way for capturing the current week (mon-sun).

Thank you all once again.  For my need the following is going to work best:

Let ([DOW=DayOfWeek (Get(CurrentDate))]

;

Case(

DOW=1; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+7; //Sun

DOW=2; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+6; //Mon

DOW=3; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+5; //Tue

DOW=4; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+4; //Wed

DOW=5; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+3; //Thu

DOW=6; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+2; //Fri

DOW=7; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+7; //Sat

""))

I'm going to PSOS  and when my weekly records are found I will just run the replace option on the found set.

• ###### 7. Re: What is the most effective way for capturing the current week (mon-sun).

Make sure you script for what will happen with replace field contents if find returns zero records or its not on the right layout or another user has record lock on a record in the found set your trying to operate on.

Their are other methods to change data in batches that are more transaction friendly than Replace Field Contents.

just sayin that you should know the gotchas of Replace Field Contents in a hosted/multi user situation before choosing it as a method.

• ###### 8. Re: What is the most effective way for capturing the current week (mon-sun).

Follow-up question regarding order of execution in case statement.  The following "DOW=6" has two possibilities.

Will the 2nd "DOW=6" always run on Friday until it's 3:00 PM, then the 1st "DOW=6" should run? will this order work?

Let ([

DOW=DayOfWeek (Get(CurrentDate));

PostHour=Hour(Get(CurrentTime))

];

Case(

DOW=1; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+7; //Sun

DOW=2; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+6; //Mon

DOW=3; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+5; //Tue

DOW=4; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+4; //Wed

DOW=5; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+3; //Thu

DOW=6 and PostHour  ≥ 14; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+8; //Fri

DOW=6; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+2; //Fri

DOW=7; Get(CurrentDate) &  "..."  &  Get(CurrentDate)+7; //Sat

" "))

• ###### 9. Re: What is the most effective way for capturing the current week (mon-sun).

CASE() will execute until it finds a match. Since you have the time great than or equal to 14:00, the it will be skipped until that hour (or greater) on that day. The second will not be used if the first one is. So it looks like you have the correct order for DOW=6!

By the way, 14:00 is 2:00 PM everywhere in the world that I know. If you need 3 pm or greater, change the '14' to '15'. That is unless I'm misunderstanding your PostHour field?

0

1

2

3

4

5

6

...

??

beverly

• ###### 10. Re: What is the most effective way for capturing the current week (mon-sun).

Thank you Beverly....typo on my part regarding the 3pm...thank you so much !!!

• ###### 11. Re: What is the most effective way for capturing the current week (mon-sun).

Hi,

So this function, given a date, will return the date of the following Sunday (or Sunday, if it's Sunday).

That is, it will group any date into a seven-day "quanta" which is designated by the last day of the week.

This should allow you to compare any week to any other week, by date.