Week starts on Friday

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi Groupies

I have created a calculated field that is supposed to specify the week a job
occurred. The week starts on Friday and goes through the following Thursday.
My calculated field looks like this:

WorkPeriod: Format(DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket
Date]),"mmm dd""/""yy") & " - " &
Format((DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket
Date])+6),"mmm dd""/""yy")

It returns a period formatted as: Dec 04/09 - Dec 10/09 (for example)

However, it is not putting the Friday's and Saturday's into the correct
week. For example November 6 and 7 2009 are showing up in the week of Oct
30 - Nov 05 and November 13 and 14 are showing up in the week of Nov 06 -
Nov 12.

Can somebody please help me straighten this out.
 
Try this --
Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]),"mmm dd/yy") &
" - " & Format(DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]),"mmm
dd/yy")
 
Perfect!

Thanks Karl!
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
KARL DEWEY said:
Try this --
Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]),"mmm dd/yy")
&
" - " & Format(DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]),"mmm
dd/yy")
--
Build a little, test a little.


CJ said:
Hi Groupies

I have created a calculated field that is supposed to specify the week a
job
occurred. The week starts on Friday and goes through the following
Thursday.
My calculated field looks like this:

WorkPeriod: Format(DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket
Date]),"mmm dd""/""yy") & " - " &
Format((DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket
Date])+6),"mmm dd""/""yy")

It returns a period formatted as: Dec 04/09 - Dec 10/09 (for example)

However, it is not putting the Friday's and Saturday's into the correct
week. For example November 6 and 7 2009 are showing up in the week of Oct
30 - Nov 05 and November 13 and 14 are showing up in the week of Nov 06 -
Nov 12.

Can somebody please help me straighten this out.
Click to expand...
Click to expand...
 
Back
Top