Results 1 to 5 of 5
  1. #1
    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. #2
    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:
    monday: dateadd([your date], 2-weekday([your date], 2))
    friday: dateadd([your date], 5-weekday([your date], 2))

  3. #3
    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. #4
    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. #5
    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
  •