Does anyone know how to ensure that when entering a list of dates in the dd mm yy format and some in the mm yy format that the mm yy dates are sorted to the end of the month rather than excel assuming I meant to put them in as the 1st?
i.e I want list to appear as
22/12/22
23/12/22
25/12/22
Dec 22
Dec 22
rather than
Dec 22
Dec 22
22/12/22
23/12/22
25/12/22
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I am not aware of any way to change this behavior in Excel. When you give Excel a month/year date (and Excel converts to a real date/time serial number), Excel will always assume the 1st of the month. The obvious solution to me is to never give Excel the chance to assume the 1st by always entering the date as well (Dec 31, 2021, for example), but that represents a change to your data entry procedure, and that might not be allowed.
If you are required to enter mmm yy and have it mean "last date of mmm yy", then you will have to figure out some way to trap the mmm yy entry and change it to last date/mm/yy. If this date entry can never be the first of the month, then a helper cell/column with something like =IF(DAY(date)=1,EOMONTH(date),date) will change all first of the month entries to the last day of the month. But that does not allow for legitimate cases where you need to enter the first of the month.
If you need to allow for legitimate cases where you enter the first of the month, then you probably end up needing to write your own procedure/algorithm for trapping the data entry as text, examine the text entry to see if it is dd/mm/yy or mmm yy, and then convert the dd/mm/yy text strings to the expected date, and convert mmm yy text strings to last of the month dates.
It's up to you how you want to approach this. IMO, it is easier to train myself and my users to never enter mmm yy dates if I/we/they mean "last of the month" by that kind of entry. Always enter the date as part of the data entry so Excel cannot make assumptions about the data I am entering. If you decide that you must allow for mmm yy entries that mean last of the month, then I think you will end up writing your own "text to date conversion" procedure that you can run in place of Excel's built in, default algorithm.
I doubt that writing such an algorithm is prohibitively difficult, but will it be worth the effort to you? How would you like to proceed?
Originally Posted by shg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks