Date Problem

[Christopher.Rohde]

I have an expression that evaluates to either a 1 or 2 based on today's date:

=IIf(DatePart('ww',(Date()),5,1) Mod 2>0,1,2)

This gives me an alternating 2 week schedule period for the year starting on a thursday.



Now, I need to return a date based on what week of the schedule period it is (1 or 2) For example, if today is Thursday of a week 1 (day1, week1) I need it to return today's date. If today is Friday of week 1, I need it to return the date of the closest Thursday (yesterday) to today and so on. If today is any day in a week 2, I need it to return the closest date of the week 1 thursday (start of the period). Example, Thursday of week 2 should return date of closest Thursday of week 1. And i need this in a single line switch or iif statement because the date returned will be used in a query as a start date to show hours worked between today and the returned date

TIA for any assistance

[June7]

You need to return the first Thursday of the 2-week period? Why Thursday? Why calculating between Thursday and current date?

Review http://www.utteraccess.com/forum/Cur...-t1875718.html

Try:

Date()-Weekday(Date(),5)+1 - IIf(DatePart("ww",Date(),5,1) Mod 2>0,7,0)

[Christopher.Rohde]

You need to return the first Thursday of the 2-week period? Why Thursday? Why calculating between Thursday and current date?

The 2 week pay period starts on a thursday. I need to calculate the total overtime hours worked so far in the current period based on date()

Review http://www.utteraccess.com/forum/Cur...-t1875718.html

Good stuff

Try:

Date()-Weekday(Date(),5)+1 - IIf(DatePart("ww",Date(),5,1) Mod 2>0,7,0)

I'll give that a try - thank you!

[Christopher.Rohde]

just had to switch the mod 2>0,7,0 to 0,7 - if there is a remainder from that calculation it is week 1 if not week 2. Thank you so much for your help!