# 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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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-comfficeffice" /><o></o>
#7/9/2010# AS TestDate, <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>

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

Replies
5
Views
840
Replies
9
Views
150
Replies
0
Views
154
Replies
18
Views
235
Replies
2
Views
1K

1,196,507
Messages
6,015,604
Members
441,905
Latest member
Jean207

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back