Week Commence Date Formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21145
Printed Date: 05 May 2024 at 12:27am
Topic: Week Commence Date Formula
Posted By: skinfreak
Subject: Week Commence Date Formula
Date Posted: 30 Oct 2014 at 4:14am
|
Hi there, I have a formula to calculate the beginning date of a field based on the week number from the year. The date in question may be any day, but I am only concerned with what the Monday for that week was. This is for a weekly reporting schedule into Excel. For example, an event occurs on Thursday, I want to calculate that week number, and then produce what the date was for Monday. The code I have used is: datevar yearstart := date(year({StartDate}),1,1); // Set the start date to the first of the year numbervar weeknumber := DATEPART("ww",{StartDate}); //NOTE: The -2 is a fudge due to a bug in Crystal which takes the beginning of week 1 as the day that Jan 1 appeared on dateadd("ww",weeknumber,yearstart)-2 You will notice that I have taken 2 off the calculation. This is because 01/01/2014 occurred on a Wednesday and so the formula is basing the start of every week on on Wednesday. This fudge is fine for now, but as the end of the year comes up, I would like a rolling 8 week reporting cycle which may not work properly. Is there another way around this? |
Replies:
Posted By: z9962
Date Posted: 30 Oct 2014 at 4:43am
|
Try this formula (change today to the required field) SELECT DayofWeek(today)
CASE 2 :today CASE 3 :dateadd ("d",-1,today) CASE 4 :dateadd ("d",-2,today) CASE 5 :dateadd ("d",-3,today) CASE 6 :dateadd ("d",-4,today) CASE 7 :dateadd ("d",-5,today) CASE 1 :dateadd ("d",-6,today) |
Posted By: DBlank
Date Posted: 30 Oct 2014 at 5:11am
|
or this may work for you...?
dateadd('ww',datediff('ww',date(1900,1,1),{StartDate}),date(1900,1,1))
|
Posted By: z9962
Date Posted: 30 Oct 2014 at 5:33am
|
These will produce different dates for Sunday... Is sunday your first day of the week? ie 26Oct = 27Oct? or the monday previous (26Oct=20Oct)? if you need the first option the DBlanks will do that, or change my Case 1 from -6 to +1
|
Posted By: skinfreak
Date Posted: 30 Oct 2014 at 5:36am
|
Originally posted by DBlank or this may work for you...? dateadd('ww',datediff('ww',date(1900,1,1),{StartDate}),date(1900,1,1)) That's the one - this is producing the week start on Monday which is perfect.  |