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
spudmccAsked:
Who is Participating?
 
mlmccCommented:
I assume it is on the one using the datefield.  Missed the closing }

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

mlmcc
0
 
BlueYonderCommented:
0
 
spudmccAuthor Commented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
mlmccCommented:
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
0
 
BlueYonderCommented:
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})
0
 
spudmccAuthor Commented:
mlmcc---getting error message--"a number is required"

Date(DateAdd('d',-DayOfWeek({SingleCopyDrawAdjust1.DrawAdjustDate}+ 1,{SingleCopyDrawAdjust1.DrawAdjustDate})))
0
 
spudmccAuthor Commented:
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).
0
 
spudmccAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.