Dates Question

1. Senior Member
Join Date
Dec 1969
Posts
1,316

## Dates Question

Im trying to generate a simple report. I want the user to input a start date and and end date (will always be 7 days difference to represent one week). I want to display sales figures for that date range and that date range for the year before. The problem is, if they enter Mar 1st - Mar 7th for this year, I cant just take Mar 1st - Mar 7th for the year before because I need to make sure that the days of the week are the same.<BR><BR>For example, a typical report would cover Monday-Sunday of a given week. I need to make sure that the prior year&#039;s dates are Monday thru Sunday...therefor the actual dates may be slightly different than Mar 1st - Mar 7th. <BR><BR>Any suggestions?

2. Senior Member
Join Date
Dec 1969
Posts
623

## RE: Dates Question

try this<BR><BR>&#039;if difference between the startdate and enddate is 7 and<BR>&#039;startdate is a monday<BR>if ( ( DateDiff ( "d", endDate, startDate ) = 7 ) and ( WeekDay ( startDate ) = 2 ) ) then<BR> &#039;Yes acceptable<BR>end if

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## RE: Dates Question

&#062; I want the user to input a start date and and end date <BR>&#062; (will always be 7 days difference to represent one week). <BR><BR>If it&#039;s always 7 days, why should the poor user have to enter two dates? It would be easier and more accurate to compute the ending date in the code.<BR><BR>On top of that, suppose your idiot user puts in July 30th as the start date. Tuesday. Instead of Monday. DOH!<BR><BR>You should allow the user to put in *ANY* date and then you "back up" to the preceding Monday (unless, of course, the user actually is smart enough to choose a Monday date).<BR><BR>As for finding the corresponding week last year... That&#039;s easy enough, but only *after* you define what "corresponding" means.<BR><BR>For example, suppose that you pick a month where the 1st is on Tuesday (Jan, 2002). So the week that includes that day starts on the last day of the prior month (31 Dec 2001 to 6 Jan 2002).<BR>But the year before, the week that included 1 Jan did *not* overlap a month (1 Jan 2001 to 7 Jan 2001). Is *that* the week you want, then? Or do you want the prior week? <BR><BR>So you have to define the terms before we can help you further.<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
623

## Waiting impatiently for Bill to kick my @#\$...

&nbsp;<BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ehhhh....why not...

...just pick the prior Monday, if the given date isn&#039;t a Monday?<BR><BR>&#060;%<BR>startDate = Request("startDate")<BR>If Weekday(startDate) = vbSunday Then<BR> &#039; back all the way to preceding Monday<BR> startDate = startDate - 6<BR>Else<BR> &#039; do the math...this works...even for Monday<BR> &#039; [weekday of Monday is 2, Tuesday 3, etc.]<BR> startDate = startDate - Weekday(startDate) + 2<BR>End If<BR><BR>endDate = startDate + 6<BR>%&#062;<BR><BR>But that was the easy part...now figure out the corresponding dates last year.<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Naw...I liked it...

And it may even be what he(?) prefers.<BR><BR>Validation of the given startdate instead of automatic fixing.<BR><BR>Who knows?<BR><BR>

7. Junior Member
Join Date
Dec 1969
Posts
10

## RE: Dates Question

The above would work however weekday has a very powerful optional parameter called firstdayofweek. Check it out in my explanation below of the whole process. <BR><BR>DateAdd(interval,number,date)<BR> interval = "yyyy"<BR> number = -1<BR> date = {your date}<BR><BR>Now you have the date a year ago<BR><BR>You can use Weekday(date,[firstdayofweek])<BR>This returns an integer telling you how many days it has been since the start of the week. By default Firstdayofweek is Sunday. You can make it equal to any of the vb constants. eg. vbMonday<BR><BR>So running your date through the function you would know how long it has been since Monday. eg 2 would mean it is Wednesday.<BR><BR>Knowing how long ago Monday was you could calculate the date<BR>again with DateAdd but using an interval = "d".<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## Almost right...

&#062; So running your date through the function you would know how <BR>&#062; long it has been since Monday. eg 2 would mean it is Wednesday.<BR><BR>Incorrect. If you want to know how many days it has been since the "firstdayofweek" that you specified, you must subtract 1 from the function value.<BR><BR>For example:<BR><BR>&#060;%<BR>theDate = DateSerial( 2002, 7, 29 ) &#039; 29 July 2002, a Monday<BR>Response.Write Weekday( theDate, vbMonday ) &#039; writes a ** 1 **<BR>theDate = DateSerial( 2002, 7, 31 ) &#039; 31 July 2002, a Wednesday<BR>Response.Write Weekday( theDate, vbMonday ) &#039; writes a ** 3 **<BR>%&#062;<BR><BR>See the point? For some idiotic reason, the first day of the week, relative to the given start day, is numbered 1, instead of zero.<BR><BR>So if you wanted to combine this trick with mine:<BR><BR>&#060;%<BR>theDate = CDate( Request("theDate") )<BR>mostRecentMonday = theDate - Weekday( theDate, vbMonday ) + 1<BR>%&#062;<BR><BR>The +1 is needed to compensate for the biased-by-1 number than Weekday gives you.<BR><BR>

9. Junior Member
Join Date
Dec 1969
Posts
10

## I hate dates!!

Yes correct. Sorry I was doing it off the top of my head.

10. Member
Join Date
Dec 1969
Posts
50

## RE: Dates Question

If you are having a calendar control from which the user is selecting the dates,then one way to work around is to disable the selection for all the weekdays except for monday(your logic should be such that the user can select only mondays from the calendar control),and don&#039;t request the user to allow the end date.As for the last week of the previous year guess you have enough solutions posted.<BR><BR><BR> o o o . <BR> _o_._&#039;_ . o <BR> ~~~~~/ Experience Is What You Get o_._0_&#039;<BR> &#039;-.-&#039; When You Don&#039;t Get ~~~0~/<BR> &#124 What You Want &#039;-.-&#039; <BR> _&#124_ &#124<BR> _&#124_ <BR><BR>:)-I see Big BILL loading guns on me<BR>-------------------------------------<BR>cheers,<BR>subbu<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•