# Calculate "LastFriday" date

#### imfarhan

##### Board Regular
Hi
The following function calculate the last "Monday" value in MS Access (SQL) but I would like to calculate the "last Friday" of the week and could some one go through how this fucntion work (break down) please. I need to get the understanding so in future I can do my self instead of copy/paste
Many thanks

#Format(DateAdd("d", -(Weekday(Now()) - 2 +7), Now()), "dd/mm/yyyy"),#

If I break the above function to get more understaning
Weekday(Now()) = 2
Don't understand -->= so the answer is =
(Weekday(Now())-2+7) = 2-2+7 ? = 7 and further
-(Weekday(Now())-2+7) = 2-2+7 ? = 7 = -7
DateAdd("d", -7, ??) = ?? what value will come on that?

Don't understand , please could some one help me on that.

Many thanks

Regards
Farhan

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### imfarhan

##### Board Regular
The following query pulls the "Last Friday", even if the current day is firday as will which I wanted in my query.

SELECT <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com ffice ffice" /><o ></o >
#7/9/2010# AS TestDate, <o ></o >
Format(DateAdd("d",IIf(Weekday([TestDate])<7,-(Weekday([TestDate])+1),6-Weekday([TestDate])),[TestDate]),"dd/mm/yyyy") AS LastFriday<o ></o >
FROM data;<o ></o >
<o > </o >
<o >If you don't want to see Last Friday if the Testday=Friday change 7 to 6</o >
<o > </o >
<o >Regards,</o >
<o >Farhan</o >

#### F&s anaylst

##### Active Member
Hi imfarhan,

if you are working with SQL bypass query you will be able to use this statement, this will return last friday date (change the 4 to different number to return different day). ensure you declare the variable before the select cause

declare @DATE datetime
set @DATE = DATEADD(wk, DATEDIFF(wk,0,GETDATE()-7), 4)
where [your time field] = @DATE