7 Replies Latest reply on Mar 29, 2011 10:09 PM by WilliamdelaMaza

# How to set a record to a particular week?

### Title

How to set a record to a particular week?

### Post

I've got a table that monitors employee attendance during the week. The work week for this particular table begins each Thursday. Currently I've got two fields that handle date information. The first is an auto-enter field (Week_start) in which the current date is entered, the other is a calculation field that adds 6 days to the former date in order to have a week (this field is called Week_end). The problem am facing now is that if a new employee is hired during the week, say on Monday, the week for this particular new employee record is going to begin on Monday and end on Sunday, disrupting my data completely. What I need for this to work, is a way to automatically set the Week_start to the nearest past Thursday so that all records begin their week on Thursday independently of the day the employee record is created. How can do this?

• ###### 1. Re: How to set a record to a particular week?

Datefield - DayofWeek ( DateField ) + 1 will return the date of the nearest preceding Sunday for any date entered into DateField. We can modify that to be:

Let ( DoW = DayOfWeek ( DateField ) ;
If ( DoW < 5 ; DateField - DoW -2 ; DateField - DoW + 5 )
)

• ###### 2. Re: How to set a record to a particular week?
Perfect. I'll give it a try right away! Thank you Phil!
• ###### 3. Re: How to set a record to a particular week?

Phil, do I have to make several  "Let" lines modifying the -2 variable for each day other than Thursday? Am asking this cause the database is still generating dates older than Thursday.

• ###### 4. Re: How to set a record to a particular week?

No, the calculation should always return a date that falls on the nearest past Thursday.

If I enter these dates | I get these dates as the start of week date

3/27/2011 (Sunday)   | 3/24/2011 (nearest past Thursday)
3/28/2011 (Monday)   | 3/24/2011
3/29/2011 (Tuesday)  | 3/24/2011
3/30/2011 (Wed.)      | 3/24/2011
3/31/2011 (Thursday) | 3/31/2011
4/1/2011 (Friday)        | 3/31/2011
4/2/2011 (Saturday)   | 3/31/2011

Isn't that what you meant by "nearest past Thursday"?

• ###### 5. Re: How to set a record to a particular week?

Yes, this is exactly what I mean by that. Thank you! Just one more question: How would I adjust that code to return a "Friday" instead of "Thursday"?

Like this:

Let ( DoW = DayOfWeek ( DateField ) ;
If ( DoW < 6 ; DateField - DoW -2 ; DateField - DoW + 6 )
)

• ###### 6. Re: How to set a record to a particular week?

Almost.

Let ( DoW = DayOfWeek ( DateField ) ;
If ( DoW < 6 ; DateField - DoW - 1 ; DateField - DoW + 6 )
)

• ###### 7. Re: How to set a record to a particular week?

Perfect! I knew that -2 had some role to play. Thank you very much Phil, I sincerely appreciate all of your kind help!