Calculate "LastFriday" date

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
102
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
 

Some videos you may like

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
Joined
Jan 29, 2010
Messages
102
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:eek:ffice:eek:ffice" /><o:p></o:p>
#7/9/2010# AS TestDate, <o:p></o:p>
Format(DateAdd("d",IIf(Weekday([TestDate])<7,-(Weekday([TestDate])+1),6-Weekday([TestDate])),[TestDate]),"dd/mm/yyyy") AS LastFriday<o:p></o:p>
FROM data;<o:p></o:p>
<o:p> </o:p>
<o:p>If you don't want to see Last Friday if the Testday=Friday change 7 to 6</o:p>
<o:p> </o:p>
<o:p>Regards,</o:p>
<o:p>Farhan</o:p>
 

F&s anaylst

Active Member
Joined
Oct 9, 2008
Messages
277
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,780
Messages
5,470,726
Members
406,719
Latest member
ensbana

This Week's Hot Topics

Top