Convert past date into number of weeks/months ago

[Ironman]

The last row in Column A contains a date.

I need the value in Column A to be expressed in A8 as "Last walk X days/weeks/months ago" as follows:

If X is less than 7 days then it's "Last walk X days ago"
If X is equal to 7 days then it's "Last walk 1 week ago"
If X is less than 1 month then it's "Last walk X weeks ago" (the number of complete weeks)
If X is equal to 1 month then it's "Last walk 1 month ago"
If X is greater than 1 month and less than 2 months then it's "Last walk X weeks ago"
If X is equal or greater than 2 months then it's "Last walk X months ago" (the number of complete months)

Many thanks!

 

[Michael M]

So what if the last date is in a cell already past A8 ??
Also, is that a 28 day month, 30 day month OR 31 day
I can see a whole lot of issues getting the right result based on the criteria !

 

[Ironman]

OK, just to say first of all that this would be triggered by a worksheet_change event.

If I've understood you correctly

1. A8 value will always change, as it's dependant on the date in the last row (say A100). So if A100 is August 9 2021 then today A8 will return "Last walk 1 month ago" and on Oct 9 if no newer entries it will be "Last walk 2 months ago" but if I go for a walk tomorrow then in 3 days' time it will be "Last walk 2 days ago".

2. Good question and it's not important to me - say 28 days if that makes it easier for you?

Thanks again!

 

[Michael M]

So this needs to be coded or a Fairly long formula ???
With only 3 criteria we are up to.......

=IF(TODAY()-A6<7,"Last walk "&TODAY()-A6&" days ago",IF(TODAY()-A6=7,"Last walk 1 week ago",IF(TODAY()-A6<31,"Last walk "&INT(DATEDIF(A6,TODAY(),"d")/7)&" weeks ago",IF(TODAY()-A6=31,"Last walk "&INT(DATEDIF(A6,TODAY(),"m"))&" month ago"))))

 

[Ironman]

Ahhhh! Thanks Michael!

Coding is fine if it's easier for you than that clunkiness

 

[Michael M]

Yeah I may not get to it today, so if someone else doesn't jump in, I'll get to it later !!

 

[Ironman]

That's no problem at all!

 

[Michael M]

Try this...it goes in the sheet module NOT thisworkbook module

Private Sub worksheet_change(ByVal target As Range)
Dim lr As Long, mv As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
If Intersect(target, Range("A" & lr)) Is Nothing Then Exit Sub
If target.Count > 1 Then Exit Sub
mv = Date - Cells(lr, 1).Value
Select Case mv
    Case Is < 7
        Cells(8, 1).Value = "Last walk " & mv & " days ago"
    Case Is = 7
        Cells(8, 1).Value = "Last walk 1 week ago"
    Case Is < 28
        Cells(8, 1).Value = "Last walk " & Int(mv / 7) & " weeks ago"
    Case Is = 28
        Cells(8, 1).Value = "Last walk 1 Month ago"
    Case Is < 56
        Cells(8, 1).Value = "Last walk " & Int(mv / 7) & " weeks ago"
    Case Is >= 56
        Cells(8, 1).Value = "Last walk " & Int(mv / 28) & " Months ago"
    Case Is < 0
        Cells(8, 1).Value = "Stop playing with VBA and go for a walk"
    End Select

End Sub

 

[Ironman]

Haha, I saw what you did, that's brilliant Michael, works perfectly, thanks a lot

 

[Michael M]

Glad to help. ..??