Cheers mate, that was what I was looking for. I had dates all over the place even with doing the sort A to Z, nothing worked. Tried your method and bang it worked a charm. Thankyou very much, have subscribed.
Thanks for subscribing. Yep dates are a real problem and worst of all it is not just Excel dependent but computer dependent. Often dates 'suddenly' go wrong when someone gets a new computer.
Thank you - This has been driving me up the wall - loads of dates in my list were not filtering as dates, despite the formatting displaying as date, but this simple fix has resolved the issue for me.
Thank you!!. At last a simple and excellent way of fixing the issue of how XL reads dates so they can be sorted. I've been trying different methods but all to no avail. Most of the supposed explanations or "fixes" I have come across - including from MS - are stupidly convoluted and don't actually address the elephant in the room which is what is explained here. Well done. This is a definite bookmark for future reference.
you are a legend ! what a total headfuk, i thought it was because i was trying to use non-use format for date. Sometimes I convert some to date but not others. some events on 12th day become December !! some work okay. - MSexcel - what an impressive way to stuff up my data !!!!!
For the one issue you mention above have a look at th-cam.com/video/RzdpVnQg2lE/w-d-xo.html . When dates start getting confused about what is a day and what is a month, there is an equally serious issue where even the ones that look like they are working are the wrong date.
Pleasure. Dates are a major issue in Excel. Some others ways to correct dates in Excel- What is a valid date- th-cam.com/video/ICPdjovXcTw/w-d-xo.html Flash Fill- th-cam.com/video/91TUcky1ANI/w-d-xo.html Text to Column- th-cam.com/video/k4acpMaOxLQ/w-d-xo.html DATEVALUE- th-cam.com/video/hUvs_G1RGtA/w-d-xo.html LEFT RIGHT MID- th-cam.com/video/HoIESJh3NR8/w-d-xo.html US and Europe dates mixed- th-cam.com/video/aYauaAxGtxQ/w-d-xo.html
saved me!!!!! thanks. is there any way that when I have exported excel data and I want to convert it to a table, this text-to-column option applies automatically? some times maybe I have many columns with this issue, I don't want to do this for each column. thank you in advance.
Absolute best would be to use powerquery (online-excel-training.auditexcel.co.za/course/get-and-transform-excels-data-cleanup-tool/ ). If you are not familiar with it then the best would be to build a template that uses formula to correct the dates.
thanks a ton, wasted so much time but no one else could see the actual problem that Excell can not see its a ddate, i can't bother writing too many formulas.
Pleasure. Dates cause lots of problems and there is only one thing you need to remember. Dates are numbers and if they are not a number they are not a date (according to Excel)
Too many different ways that dates are shown around the world. Same set of characters can be 2 different dates depending on whether the person entering them is European or American.
do you know of ay way to do this within a VBA macro? I have a large file and I'm automating a process pulling data from multiple sources and looking up dates to check. When I look up dates, some are stored as values and some are stored as text which fails within a lookup.
Have a look at th-cam.com/video/aYauaAxGtxQ/w-d-xo.html . Not exactly the same but I would suggest you rather use a formula to do this. Hopefully there is some consistency e.g. all the 'text' dates are mdy or some thing like that. The other option would be to use something like PowerQuery and 'clean' the dates just after there source before they are all jumbled togeher- see th-cam.com/video/i7yFU3fT5UA/w-d-xo.html as an idea
How about the original format is shown in other ways such as Jul 21, 2022? I tried your approach but it doesn't apply to this situation. Would you mind sharing how to fix issue like that? TIA
So the concept will be the same, it is just how to get that date format to convert into a date that Excel recognizes as a date. It is a little bit computer/ region dependent. On my computer, using your format of Jul 21, 2022 the Text to Column trick worked ( see th-cam.com/video/k4acpMaOxLQ/w-d-xo.html ) where I indicate the date format as MDY . If you want to fully understand dates do these video clips in this order as it goes from understanding the issue to fixing it. th-cam.com/video/ICPdjovXcTw/w-d-xo.html th-cam.com/video/91TUcky1ANI/w-d-xo.html th-cam.com/video/k4acpMaOxLQ/w-d-xo.html th-cam.com/video/hUvs_G1RGtA/w-d-xo.html th-cam.com/video/HoIESJh3NR8/w-d-xo.html th-cam.com/video/aYauaAxGtxQ/w-d-xo.html th-cam.com/video/Ch3ez1YIyH4/w-d-xo.html th-cam.com/video/hmVIcm-dXnY/w-d-xo.html th-cam.com/video/cZWEgRukYL4/w-d-xo.html th-cam.com/video/D2rR6Oznpak/w-d-xo.html th-cam.com/video/XLTwJhjeAkU/w-d-xo.html th-cam.com/video/RzdpVnQg2lE/w-d-xo.html
What if it works with some, but doesn't work with all the dates? Because most of the dates do get fixed but a lot are left in the original mm/dd/yyyy format.
Very dangerous situation. If they all come from a system (so the raw data is consistent) then there is a good chance that the ones that look OK are actually wrong in that it has switched the month and the day. Have a look at th-cam.com/video/aYauaAxGtxQ/w-d-xo.html for this issue. Confirm if this is it, otherwise it might be something else.
Have a look at th-cam.com/video/P6xjxoRM4_M/w-d-xo.html . I think in this file at 01:10 I used the CTRL + SHIFT + down arrow (can't really remember but th-cam.com/video/P6xjxoRM4_M/w-d-xo.html shows various ways to highlight and move around cells.
Just had a look. Doesn't seem to be able to be done in the normal tool. Perhaps use the tool to split the columns and recreate the date with DATE function which works in Google Sheets
Please how to undo this? I completely ruined my file by trying to sort the data. It's now all random dates at places it shouldn't be. I can't undo it anymore or recover older versions of the file... Any advice?
You need to understand how the dates were wrong and then build a formula to fix them. Closest example we have is where US and European date formats are mixed up at th-cam.com/video/aYauaAxGtxQ/w-d-xo.html . Hope that helps
Something must have changed in the source data or your computer with regards the date format. Perhaps change your computer regional settings to match the type of dates you are getting in the Excel file.
![[TH] VALORANT Champions Seoul - Grand Final - EDG vs TH](http://i.ytimg.com/vi/WvObMc9VelI/mqdefault.jpg)
Exactly what I needed, all the other simple guides were just showing how to sort and ignoring the issue of Excel not seeing a date as just that.
Great. Dates are a real challenge in Excel
Cheers mate, that was what I was looking for. I had dates all over the place even with doing the sort A to Z, nothing worked. Tried your method and bang it worked a charm. Thankyou very much, have subscribed.
Thanks for subscribing. Yep dates are a real problem and worst of all it is not just Excel dependent but computer dependent. Often dates 'suddenly' go wrong when someone gets a new computer.
Thank you - This has been driving me up the wall - loads of dates in my list were not filtering as dates, despite the formatting displaying as date, but this simple fix has resolved the issue for me.
Glad it helped. Once you understand dates in Excel you can solve most problems.
Thank you!!. At last a simple and excellent way of fixing the issue of how XL reads dates so they can be sorted. I've been trying different methods but all to no avail. Most of the supposed explanations or "fixes" I have come across - including from MS - are stupidly convoluted and don't actually address the elephant in the room which is what is explained here. Well done.
This is a definite bookmark for future reference.
Pleasure. Understanding how dates work in Excel makes it much easier to handle most problems.
Clear and to the point, exactly what I needed. Thank you!
You're welcome!
Love your clear explanation, exactly what I was searching for, thank you so much!
You're very welcome!
Thank you so much for making this guide. I lost couple hairs figuring out this simple solution!
Glad it helped. Dates are a big issue in Excel
Ahh thank you my good man, exactly what I was looking for. Every other video was about pivot tables not recognizing dates. Have a wonderful day.
Great to hear!
I tried so many options but it didnt work. Finally tried your steps and it worked. Thank you.
Great to hear. Dates being seen as text are a real problem in Excel and worse it is often computer dependent!
Perfect! Thank you, this is what I needed in order to continue with my work!
Glad it helped!
This helped me so much, thank you! Your voice also makes Excel stuff more interesting to listen to.
Pleasure- Dates are a major problem in Excel as they often look like they are not working. Accent = 🇿🇦
This save me chucking my computer out of the window! thank you
😀
Life saver, thank you!
Thanks a lot mate, this problem was getting super annoying.
Glad it helped
THANK YOU! This is the only way I could get this to work with a report I am running.
Great to hear that you sorted out your date issue.
you are a legend ! what a total headfuk, i thought it was because i was trying to use non-use format for date. Sometimes I convert some to date but not others. some events on 12th day become December !! some work okay. - MSexcel - what an impressive way to stuff up my data !!!!!
Great. Dates in Excel are probably the most confusing thing.
meant to say "non-usa format"
For the one issue you mention above have a look at th-cam.com/video/RzdpVnQg2lE/w-d-xo.html . When dates start getting confused about what is a day and what is a month, there is an equally serious issue where even the ones that look like they are working are the wrong date.
I tried so many different things, this worked!!!!
Great to hear! Once you understand this about Excel dates your Excel work will be much easier!
F hell the text to columns trick was the only solution that worked for me thank you! All these other tutorials just telling you to format cells =p
You're welcome!
Exactly what I needed, great video!
Glad to hear it! Once you understand dates in Excel it solves a lot of your problems.
Thank you so much for the superb video.
Our pleasure! Dates can be confusing in Excel and cause lots of problems
Hi, That was very Helpful. Thank you very much for sharing.
Pleasure. Dates are a major issue in Excel. Some others ways to correct dates in Excel-
What is a valid date- th-cam.com/video/ICPdjovXcTw/w-d-xo.html
Flash Fill- th-cam.com/video/91TUcky1ANI/w-d-xo.html
Text to Column- th-cam.com/video/k4acpMaOxLQ/w-d-xo.html
DATEVALUE- th-cam.com/video/hUvs_G1RGtA/w-d-xo.html
LEFT RIGHT MID- th-cam.com/video/HoIESJh3NR8/w-d-xo.html
US and Europe dates mixed- th-cam.com/video/aYauaAxGtxQ/w-d-xo.html
Thank you sir.. Love from odisha
Glad it helped
Omg! You're a life saver!! Thank u
You're welcome!
Thanks, this sorted it for me
Great. Dates often give problems in Excel
Exactly what I was looking for. Thanks Mate !
Great to hear. Once you understand how dates work in Excel, most business problems are solved
Thank you. Well explained. Worked perfectly.
Glad it helped. Dates in Excel are an important area
Stupid Helpful. You the man!!!
Thanks
saved me!!!!! thanks. is there any way that when I have exported excel data and I want to convert it to a table, this text-to-column option applies automatically? some times maybe I have many columns with this issue, I don't want to do this for each column.
thank you in advance.
Absolute best would be to use powerquery (online-excel-training.auditexcel.co.za/course/get-and-transform-excels-data-cleanup-tool/ ). If you are not familiar with it then the best would be to build a template that uses formula to correct the dates.
Thank you kind sir!
Very welcome! Dates can be a real problem. We have a playlist on Date issues you can subscribe to
thank you
this saved me million hours! thank you!
Glad it helped
thanks a ton, wasted so much time but no one else could see the actual problem that Excell can not see its a ddate, i can't bother writing too many formulas.
Glad it was useful. Dates can be a real issue in Excel.
Thank you so much bro!
Pleasure. Dates cause lots of problems and there is only one thing you need to remember. Dates are numbers and if they are not a number they are not a date (according to Excel)
@@Advanced_Excel It must have some logic lol
Perfect! But... Why do we have to go through these steps? Why doesn't it work the way we do it intuitively?
Too many different ways that dates are shown around the world. Same set of characters can be 2 different dates depending on whether the person entering them is European or American.
I love this! thank you so much it really helped me!
You're so welcome! Once you understand how dates work in Excel you will never have a time consuming problem again!
Thank you
Welcome!
Very clear and concise. Thank you
Pleasure
THANK YOU!!!
Pleasure. Understanding dates in Excel is important to reduce the Excel problems you have.
do you know of ay way to do this within a VBA macro? I have a large file and I'm automating a process pulling data from multiple sources and looking up dates to check. When I look up dates, some are stored as values and some are stored as text which fails within a lookup.
Have a look at th-cam.com/video/aYauaAxGtxQ/w-d-xo.html . Not exactly the same but I would suggest you rather use a formula to do this. Hopefully there is some consistency e.g. all the 'text' dates are mdy or some thing like that. The other option would be to use something like PowerQuery and 'clean' the dates just after there source before they are all jumbled togeher- see th-cam.com/video/i7yFU3fT5UA/w-d-xo.html as an idea
This is gold! Thank you!
Glad it helped you. Dates are a common problem in Excel and often look like they are not working
How about the original format is shown in other ways such as Jul 21, 2022? I tried your approach but it doesn't apply to this situation. Would you mind sharing how to fix issue like that? TIA
So the concept will be the same, it is just how to get that date format to convert into a date that Excel recognizes as a date.
It is a little bit computer/ region dependent. On my computer, using your format of Jul 21, 2022 the Text to Column trick worked ( see th-cam.com/video/k4acpMaOxLQ/w-d-xo.html ) where I indicate the date format as MDY .
If you want to fully understand dates do these video clips in this order as it goes from understanding the issue to fixing it.
th-cam.com/video/ICPdjovXcTw/w-d-xo.html
th-cam.com/video/91TUcky1ANI/w-d-xo.html
th-cam.com/video/k4acpMaOxLQ/w-d-xo.html
th-cam.com/video/hUvs_G1RGtA/w-d-xo.html
th-cam.com/video/HoIESJh3NR8/w-d-xo.html
th-cam.com/video/aYauaAxGtxQ/w-d-xo.html
th-cam.com/video/Ch3ez1YIyH4/w-d-xo.html
th-cam.com/video/hmVIcm-dXnY/w-d-xo.html
th-cam.com/video/cZWEgRukYL4/w-d-xo.html
th-cam.com/video/D2rR6Oznpak/w-d-xo.html
th-cam.com/video/XLTwJhjeAkU/w-d-xo.html
th-cam.com/video/RzdpVnQg2lE/w-d-xo.html
Thank you soooo much 😊
Pleasure- dates are the most misunderstood items in the Excel world!
What if it works with some, but doesn't work with all the dates? Because most of the dates do get fixed but a lot are left in the original mm/dd/yyyy format.
Very dangerous situation. If they all come from a system (so the raw data is consistent) then there is a good chance that the ones that look OK are actually wrong in that it has switched the month and the day. Have a look at th-cam.com/video/aYauaAxGtxQ/w-d-xo.html for this issue. Confirm if this is it, otherwise it might be something else.
thanks!!!
Please. Excel dates can be very confusing and often don't look like they are working.
How did you highlight your columns at 1:10?
Have a look at th-cam.com/video/P6xjxoRM4_M/w-d-xo.html . I think in this file at 01:10 I used the CTRL + SHIFT + down arrow (can't really remember but th-cam.com/video/P6xjxoRM4_M/w-d-xo.html shows various ways to highlight and move around cells.
How do you do this on a Google excel share doc? The text to column layout is different?
Just had a look. Doesn't seem to be able to be done in the normal tool. Perhaps use the tool to split the columns and recreate the date with DATE function which works in Google Sheets
@@Advanced_Excel perfect, worked now! Thank you
Pleasure
Please how to undo this? I completely ruined my file by trying to sort the data. It's now all random dates at places it shouldn't be. I can't undo it anymore or recover older versions of the file... Any advice?
You need to understand how the dates were wrong and then build a formula to fix them. Closest example we have is where US and European date formats are mixed up at th-cam.com/video/aYauaAxGtxQ/w-d-xo.html . Hope that helps
Thank you soooo much i was about to punch a hole in my monitor
Pleasure. Dates in Excel can cause that level of frustration.
Thank you!
Welcome!
Nice one dude :) Thank you.
No problem
thank u :)
No problem 😊. Understanding dates in Excel solves a lot of headaches
Is there a way to prevent this permanently? This just started occurring.
Something must have changed in the source data or your computer with regards the date format. Perhaps change your computer regional settings to match the type of dates you are getting in the Excel file.