Thread: Date of First Day of Week

1. Registered User
Join Date
Dec 2002
Location
Washington D.C.
Posts
164

Unanswered: Date of First Day of Week

Hi,
I have a table that shows the work days of employees and the hours they have worked.
I need to calculate the Over time for each employee per week.
However I also need to adjust my calculation based on pay periods.
In any case, I have grouped ny data based on the week number, however I need to see what the Date of the first day and last day of every week is.

Can any one help me?

2. Registered User
Join Date
Mar 2003
Location
London
Posts
40

Re: Date of First Day of Week

Originally posted by Sia
Hi,
I have a table that shows the work days of employees and the hours they have worked.
I need to calculate the Over time for each employee per week.
However I also need to adjust my calculation based on pay periods.
In any case, I have grouped ny data based on the week number, however I need to see what the Date of the first day and last day of every week is.

Can any one help me?
If I understand correctly you want to get Monday's and Friday's dates from a date, then you can use:

3. Registered User
Join Date
Dec 2002
Location
Washington D.C.
Posts
164
Thanks, your solution provides me with an alternative, however my goal is to get the DATE of the Monday or Friday of a WEEK given only the WEEK NUMBER and Year.
For example I need to now the Monday Date of the 32nd Week of Year 2002.
In other words if all you know is 33/2002 (representing the 33rd week of year 2002) can you give me the date of the Monday for that week?

Thanks

4. Registered User
Join Date
Mar 2003
Location
London
Posts
40
Originally posted by Sia
Thanks, your solution provides me with an alternative, however my goal is to get the DATE of the Monday or Friday of a WEEK given only the WEEK NUMBER and Year.
For example I need to now the Monday Date of the 32nd Week of Year 2002.
In other words if all you know is 33/2002 (representing the 33rd week of year 2002) can you give me the date of the Monday for that week?

Thanks
I assume that you use cint(format([your date], "ww", 2)) to get you week number, i.e. your weeks start from monday

then your monday date for week lngWeek (e.g. 33) and year lngYear (e.g. 2003) will be:
dateadd("d", (lngWeek-1)*7+ 1-weekday(dateserial(lngYear, 1, 1),2), dateserial(lngYear, 1,1))

5. Registered User
Join Date
Dec 2002
Location
Washington D.C.
Posts
164
Thanks

Originally posted by yk58301
I assume that you use cint(format([your date], "ww", 2)) to get you week number, i.e. your weeks start from monday

then your monday date for week lngWeek (e.g. 33) and year lngYear (e.g. 2003) will be:
dateadd("d", (lngWeek-1)*7+ 1-weekday(dateserial(lngYear, 1, 1),2), dateserial(lngYear, 1,1))

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•