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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

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

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.