asked on

Calculate Sunday in given week.

Hi Experts

Need your assistance please. I have a field "date" in a report that I then need to calculate the Sunday date of the week that the date falls.
The date field is actually formatted as a date (mm/dd/yyyy) if that is relevant.

Your help is greatly appreciated and I thank you in advance.

Andy

BlueYonder

Here are instructions http://crystaltricks.com/wordpress/?p=155.

spudmcc

ASKER

Maybe I am missing something but your solution is giving me the "day name" for my date. I don't need this but rather need the date of the last day of that week (Sunday). The results should always be a Sunday.

Mike McCracken

Try this formula based on currentdate

Date(DateAdd('d',-DayOfWeek(CurrentDate)+ 1, CurrentDate))

If you are using a database field then change CurrentDate to the field

Date(DateAdd('d',-DayOfWeek({YourDateField)+ 1, {YourDateField}))

mlmcc

BlueYonder

If DayOfWeek({@Today}) = 2 Then
dateadd ("d",-1,{@Today})
Else If DayOfWeek({@Today}) = 3 Then
dateadd ("d",-2,{@Today})
Else If DayOfWeek({@Today}) = 4 Then
dateadd ("d",-3,{@Today})
Else If DayOfWeek({@Today}) = 5 Then
dateadd ("d",-4,{@Today})
Else If DayOfWeek({@Today}) = 6 Then
dateadd ("d",-5,{@Today})
Else If DayOfWeek({@Today}) = 7 Then
dateadd ("d",-6,{@Today})
Else If DayOfWeek({@Today}) = 1 Then
dateadd ("d",-7,{@Today})

spudmcc

ASKER

mlmcc---getting error message--"a number is required"

Date(DateAdd('d',-DayOfWeek({SingleCopyDrawAdjust1.DrawAdjustDate}+ 1,{SingleCopyDrawAdjust1.DrawAdjustDate})))

spudmcc

ASKER

BlueYonder

Your solution is giving me the Sunday before the date. What I need is Sunday as the last day of the week. If my date is 12/13/2013 which is a Friday I need 12/15/13 which would be the last day of the week (Sunday).

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spudmcc

ASKER

Thanks so much for your elegant solution. It still was using Sunday as the 1 day of the week so for example if I had a date of 12/13/13 (Friday) it was returning 12/8/13 (Sunday) where I wanted 12/15/13 (Sunday). This would make Sunday the last day of the week. I just added 7 to your formula and it gave me the proper date required.

Appreciate your time, effort and sharing your knowledge. Much thanks!

Andy