Convert past date into number of weeks/months ago

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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!
 
Glad to help. ..??
While you're there...

What would it need to look like...

1. If it was a Worksheet_Activate event instead?
2. To return "Last walk today" and "Last walk yesterday"?

Thanks again :)
 
Last edited:
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
But if you made it a ws activate the last row wouldnt be todays date...it would be the last previous date
2. In that case you would need to start looking at elapsed hours...??
 
Upvote 0
Ahhh OK - no worries, thanks for educating me again :)

I was just wondering what would make the time elapsed change if nothing changed in the sheet - would it need to be a Workbook_Open Event instead?

Edit - I was playing around with it and I got this to work for "today" and "yesterday"
VBA Code:
Case Is = 0
    Cells(8, 1).Value = "TODAY"
    Case Is = 1
    Cells(8, 1).Value = "YESTERDAY"
 
Last edited:
Upvote 0
Ahhh OK - no worries, thanks for educating me again :)

I was just wondering what would make the time elapsed change if nothing changed in the sheet - would it need to be a Workbook_Open Event instead?

Edit - I was playing around with it and I got this to work for "today" and "yesterday"
VBA Code:
Case Is = 0
    Cells(8, 1).Value = "TODAY"
    Case Is = 1
    Cells(8, 1).Value = "YESTERDAY"
    Case Is < 7 > 1
        Cells(8, 1).Value = mv & " DAYS AGO"
You could make it a worksheet_selection change event
Cool..so what do you need me for...??
 
Upvote 0
:LOL::LOL::LOL: that's brilliant, thanks a lot - my brain needs feeding :)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top