How to get previous week’s Monday’s and Friday’s date based on today’s date.

[ThrottleWorks]

Hi,
I need to write a formula, which will give me previous week’s Monday’s and Friday’s date based on today’s date.
For example, today’s date in 15-05-2017, so formula should give me 08-05-2017 and 12-05-2017. I will be happy if I need to apply two different formulas.

Can anyone please help me in this.

 

[Deepak]

B2 =TODAY()-5-WEEKDAY(TODAY())
C2 =TODAY()-1-WEEKDAY(TODAY())

 

[Chihiro]

Previous Monday...
=TODAY()-WEEKDAY(TODAY()-2)

Previous Friday...
=TODAY()-WEEKDAY(TODAY()-6)

 

[chirayu]

Here's a macro solution to boot

Sub Mon_Fri()

Dim LastMon As Date
Dim LastFri As Date
Dim CurrentDay As Integer

CurrentDay = Weekday(Date, vbSunday)

'LastMon
If CurrentDay = 1 Then
    LastMon = Date - 6
ElseIf CurrentDay = 2 Then
    LastMon = Date - 7
ElseIf CurrentDay = 3 Then
    LastMon = Date - 8
ElseIf CurrentDay = 4 Then
    LastMon = Date - 9
ElseIf CurrentDay = 5 Then
    LastMon = Date - 10
ElseIf CurrentDay = 6 Then
    LastMon = Date - 11
ElseIf CurrentDay = 7 Then
    LastMon = Date - 12
Else
End If

'LastFri
If CurrentDay = 1 Then
    LastFri = Date - 2
ElseIf CurrentDay = 2 Then
    LastFri = Date - 3
ElseIf CurrentDay = 3 Then
    LastFri = Date - 4
ElseIf CurrentDay = 4 Then
    LastFri = Date - 5
ElseIf CurrentDay = 5 Then
    LastFri = Date - 6
ElseIf CurrentDay = 6 Then
    LastFri = Date - 7
ElseIf CurrentDay = 7 Then
    LastFri = Date - 8
Else
End If

End Sub

 

[ThrottleWorks]

Hi @Deepak sir, thanks a lot for the help. Good night.

Hi @Chihiro sir, thanks a lot for the help. Good night.

Hi @chirayu, thanks a lot for the help. Good night.