Hi,
I have a column in excel with dates (currently in the format "Apr '13", but I don't mind changing that), is there a way to have in the adjacent cell a message that flags up if that value is older than 12 months compared to the current date?
Any help appreciated greatly.
Hi,
With date in A1
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Sorry,
I'm a bit thick, the format I have is "Apr '13", meaning April 2013.
Is this right, should that formula work, or what date format would be better?
Hi
You said in your original that you had DATES in the column but formatted as MMM YY.
Formatting isn't important, it has no effect on calculations of the sort you're doing. What's important is that the dates must be proper date numbers and not just strings of text that happen to look like a date. I suspect you actually have text strings with the CHARACTERS "Apr 13",. You can check by testing with
=ISNUMBER(A1) which should return TRUE if the 'date' is in fact a proper date. If you're going to want to do date arithmetic, like counting wheher one date is before another then the dates MUST be proper date numbers.
If they're not then the sensible thing to do is to make them date numbers otherwise you're into an inelegant solution of having to use string slicing techniques to recreate a proper date number in a helper column.
Very helpful. Solved.
My pleasure and thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks