Please help me decipher this code

[April15Hater]

Hey guys-

I found a piece of code in one of my searches to return the previous Friday of the current week and it works great for what I'm trying to do. But I think it can be helpful in other areas as well, and I just can't for the life of me figure out what is making it tick (I'm rather new to date serials too so that might be part of the problem). I even have the msdn printouts of what the arguments are, and it looks simple, but I can't figure it out. Could somebody please explain to me how this is working? Thanks!

DateDiff("d", DatePart("w", Now()-1), Now() )

 

[KenHigg]

DatePart("w", Now()-1)

Takes todays date and time, the 'now' part, and subtracts one day from it. So something like 11/5/2008 13:23:20

Then the DatePart says take that date/time and tell me what week number of the year is it in. So something like 46. (just a guess).

So the third thing it does with the DateDiff is tell me the difference in week 46 and now.

It kind of is jumbled looking but if you say it works then I'll take your word for it.

 

[April15Hater]

See, I don't see how that is returning Sunday for the current week everytime no matter what day of the week you are currently on(I said friday before but it was actually sunday). By the way, I have the clause in a Where Command.

 

[KenHigg]

I'm guessing its first day of the week the "w" gives you. So if the "w" gives you say week 42 of this year, then when you do the date diff thing it has to have an actual day which be something like 'whats the first day of the week for week 42'. Of course then it always returns the sunday which is the first day of the week...

(That makes my head dizzy )

 

[April15Hater]

Well I'm glad I'm not the only one. So when it returns 42, is it week 42 of 2008, or is it simply the integer 42? So is there an easier way of accomplishing that statement?

 

[KenHigg]

If I had to guess, when ask for the week number of a day, it actually gets the day for the first day of the week, in this case whatever day sunday is. Then is does a little quick math to tell you it was week number 42. If not, how else could it do the date diff thing...???

 

[April15Hater]

I see what you are saying. If i recall correctly though when I got the code it was set to friday and the the code read -3 instead of -1

 

[raskew]

Hi -

From the debug (immediate) window:

? date() - (WeekDay(date()) + IIf(WeekDay(date()) <= 6, 7, 0) - 6)

... will return the previous Friday (Sun = 1, Mon = 2 thru Sat = 7).

Replace the 6 with your weekday of choice to return other weekdays.

Bob

 

[April15Hater]

Hi Bob-

That code is exactly what I'm trying to do. Although I did modify it to make the weeks variable:

? (date()-(intWeek-1)*7) - (WeekDay(date()) + IIf(WeekDay(date()) <= 6, 7, 0) - 6)

But I feel.....fake. I kinda plugged at it until it returned what I wanted, but I always feel icky when I do that; I feel better knowing exactly what I did, but I'm having trouble deciphering this one as well.

 

[raskew]

Take what was previously provided (today is Monday, 10 Nov 08)

? date() - (WeekDay(date()) + IIf(WeekDay(date()) <= 6, 7, 0) - 6)
11/7/2008

...and use the DatePart() function to return the week of the above date:

? datepart("ww", date() - (WeekDay(date()) + IIf(WeekDay(date()) <= 6, 7, 0) - 6))
 45

Ensure you use "ww" (week) and not "w" (weekday).

HTH - Bob

 

[datAdrenaline]

Just a quick note ...

DatePart("w", Now()-1)
Does NOT return the week number of the year, it returns the day integer for the day of the week

So ...

DatePart("w", #11/10/2008#-1) --> DatePart("w", #11/09/2008#) = 1 (which is typically Sunday)

But do take note that the system settings have an effect on the value returned, you see there is a 3rd argument to the DatePart() function which is used to indicate what you want to be called the first day of the week, which MOST systems that is set to Sunday, however ...

DatePart("w", #11/10/2008#-1, vbMonday) --> DatePart("w", #11/09/2008#, vbMonday) = 7 ... which means if Monday was day 1, then Sunday is day 7 ...

Many of the date functions have this argument, so be careful as the expression proposed by raskew (namely the Weekday() function) has the "first day of week argument" as well.

....

Ok ... with that established and going back to the original Question, let break down the expression ...

With Now() returning today, which is 11/10/2008 10:46 AM we have:

DateDiff("d", DatePart("w", Now()-1), Now() )
----
Get the day number of yesterday with ...
DatePart("w", Now()-1)
--> DatePart("w", #11/10/2008 10:46 AM#-1)
--> DatePart("w", #11/09/2008 10:46 AM#) = 1 (Sunday, assuming Sunday is day 1)

It is important to note, at this point, that Dates to Access and VBA are simply the number of days passed a base date. That base date in VBA and Access is 12/30/1899 12:00 AM. So in the next step, the expression in question OFFSETS the base date by the day number that was acheived in the first step of the expression. So the expression asked how many days are between 1 (12/31/1899) and Now (11/10/2008) ... which will be one day shy of how many days are between 0 (12/31/1899) and Now (11/10/2008)

DateDiff("d", 1, #11/10/2008 10:46 AM#) = 39761 days

Now, remember to Access/VBA, a date is a number, specifically the number of days passed the base date, so the serial number of 39761 (which is our final result) is 39761 days passed the base date, which is translated to a date of 11/09/2008 ...

You can get that value in a couple of ways ...
CDate(39761) = 11/09/2008
..Or..
DateAdd("d",39761,0) = 11/09/2008

....

So ... that is how the original expression gets the first day of the current week, it just offsets the base date by the day number of yesterday ... pretty creative really. But do note, that to get a specific day of the week, like what was last Friday ... you would indicate that Friday is the "first day of the week" and your expression would become ...

DateDiff("d", DatePart("w", Now()-1,vbFriday), Now(),vbFriday)

Which with today being 11/10/2008, will yeild 11/07/2008, once the raw number of days is converted to a date.

But becareful with this too ... because if we plug in a date that is a Friday, it will return LAST Friday's date ...

CDate(DateDiff("d", DatePart("w", #11/14/2008#-1,vbFriday), #11/14/2008#,vbFriday)) = 11/07/2008

...

{Note: all date formats in this post are US format mm/dd/yyyy}

 

[April15Hater]

I see, so no matter what week it is, the datepart will eliminate the difference between now and the base with the remainder representing the day of the week. Makes sense now! Thanks!

 

[datAdrenaline]

Glad to be of service! ...