What date is last week Friday? 2

[siebel2002]

I would appreciate any help in snippet of code to determine the date for last Friday. Thank you..

 

[LittleSmudge]

You can use the WeekDay function to return the day of the week that today is

So

Today       Weekday 
Monday         1
Tuesday        2
Wednesday      3
Thursday       4
Friday         5
Saturday       6
Sunday         7

Date() - 7 take you to today's day LAST week
Then add back 5 ( for Friday ) Minus the WeekDay

So Friday last week = Date() - 7 + ( 5 - WeekDay )

Which simplifies to = Date() - 2 - WeekDay

You might need to adjust the -2 if your difinition of week ending date disagrees with mine.


'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[mpastore]

lastfri: [dDt] - IIf(Format([dDt], "w&quot = 7, 1, Format([dDt], "w&quot + 1)

 

[siebel2002]

Thank you so much.. Great help..

 

[raskew]

And an approach similiar to mpastore's using a function
which allows the user to specify the specific day of
week prior to the specified date:

Function LastNDay(pDay As Date, wday As Integer) As Date
'*******************************************
'Name:      LastNDay (Function)
'Purpose:   Find date of  day of week (Sun, Mon, etc)
'           preceding a specified date
'Inputs:    (1) ? LastNDay(date(), 3)      'Tuesday
'           (2) ? LastNDay(#28-May-03#, 6) 'Saturday
'Output:    (1) 5/27/03 'Today's date = 5/29/03
'           (2) 5/23/03
'*******************************************

LastNDay = [pDay] - (WeekDay([pDay]) + IIf(WeekDay([pDay]) <= wday, 7, 0) - wday)

End Function

 

[siebel2002]

Good morning Raskew:

What an elegant solution indeed!

Thank you

 

[TLady]

Public Function Initalize_Report_TimePeriod(DateIn As Date) As Variant

ReportEndDate = DateIn - WeekDay(DateIn, vbSaturday) ' Friday of Last Week

End Function


HA ???

TIA

 

[mpastore]

Hey, TLady, you rock

Mike Pastore

Hats off to (Roy) Harper

 

[TLady]

I have more of these, ask if you need some.
Thanks,
I feel like a STAR !

TIA

 

[raskew]

So is the idea that you have seven functions, one for each day of the week? Hmmm!