Please leave a comment below with a technique you would use to convert this text to a date value. There is no wrong answer here, so please feel free to share. I will then create a follow-up video with the suggested solutions. Thank you! :-)
Ctrl + H (replace function) Find value = CDT 2017 Replace with = (keep it blank) Press Alt + A (Replace All) It will automatically converted into a date formate. Try it... Have a nice day, nice video to
Thanks for the video. I have never used the Flash Fill functionality, but will in the future. My first thought on your challenge was to use the Text to Columns under Data. I have used this previously on many occasions. Looking at the data set in your example you have a pattern of Day, Month and Day numbers which are all 3 digits and could easily be moved to separate columns. Then the time stamp (8 digits) which could also be pared to another column. The end result might not be exactly what you want at that point. But it could be possible to also use Find and Replace: Find the first three digits and replace them with nothing; then Find and Replace all instances of CDT and replace them with nothing; then format the remaining date and timestamp accordingly.
Great tip on Flash Fill. I tried it and it works with some tweaking of the date format (my default is different than US MM/dd/YY). However, if some the dates in the first column were changed to a different year (I tried 2012) the conversion ended up with all dates moved to 2017. In such case it may be easier to parse the dates using either left/mid/right functions or doing conversion of text to columns, then combining the parts into the date.
Excel would not read converted bank statement dates e.g. 23Mar.......i used your suggestion and voila , in a matter of seconds i could change the entire column of unreadable dates and also back date to the year 2019. thank you!
Hi, could you help me? When I tried to follow the steps, Excel did not do what you showed in the video, instead it gives me an error saying "Please double check the FLash Fill results. We encountered some input cells with values longer then 255 characters, and we had to skip them. " I am trying extract the date from data like "Thu, 01 Aug 2024 20:48:52" to any reconginzable date format (e.g. like '2024-01-08' or '08/01/2024')
What about the TimeZone and/or Daylight Savings Indicatior? shouldn't there be a new Column or Columns for each: "TimeZone" and Daylight Savings Y/N" ???
Hi There, many thanks for the excellent description. I am learning excel through your tutorial . I have question here if we want to do any automation how can it be done? To begin with any small example. I have query , there is dataset includes start date and end date of multiple passengers. I need to find unique total number of travel days of the passenger and this is continuous process .
Hi Saint! 😊 Try right-clicking the cell, and choosing Format Cells. In the Format Cells window that appears, select the Number tab at the top, then choose Custom as the Category. In the Type textbox, enter mmm-d-yyyy, then click OK. I hope that helps you! 😊
I would have just taken the initial data and used "Text to Columns", selecting "Fixed Width", and then moving the lines to collate the data together as you see fit.
Not a terribly elegant solution, but it works. =DATE(RIGHT(A2,4),VLOOKUP(MID(A2,5,3),{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,0),MID(A2,9,2)+0)+MID(A2,12,8)+0
Better version, based upon Tom Urtis pointer on Twitter [Using Month(1&"Jan") to convert to month number]: =DATE(RIGHT(A2,4),MONTH(1&MID(A2,5,3)),MID(A2,9,2)+0)+MID(A2,12,8)+0
Please leave a comment below with a technique you would use to convert this text to a date value. There is no wrong answer here, so please feel free to share. I will then create a follow-up video with the suggested solutions. Thank you! :-)
Ctrl + H (replace function)
Find value = CDT 2017
Replace with = (keep it blank)
Press Alt + A (Replace All)
It will automatically converted into a date formate.
Try it... Have a nice day, nice video to
This was great! I appreciate you a million times. I have been looking for this for a while now
You are such an excel wizard. I learned in a few days what I took years to put together by watching 3 of your videos over and again.
Thanks Fayia! ;-)
Thanks for the video. I have never used the Flash Fill functionality, but will in the future.
My first thought on your challenge was to use the Text to Columns under Data. I have used this previously on many occasions. Looking at the data set in your example you have a pattern of Day, Month and Day numbers which are all 3 digits and could easily be moved to separate columns. Then the time stamp (8 digits) which could also be pared to another column. The end result might not be exactly what you want at that point.
But it could be possible to also use Find and Replace: Find the first three digits and replace them with nothing; then Find and Replace all instances of CDT and replace them with nothing; then format the remaining date and timestamp accordingly.
Great tip on Flash Fill. I tried it and it works with some tweaking of the date format (my default is different than US MM/dd/YY). However, if some the dates in the first column were changed to a different year (I tried 2012) the conversion ended up with all dates moved to 2017. In such case it may be easier to parse the dates using either left/mid/right functions or doing conversion of text to columns, then combining the parts into the date.
Thank you so very much, I was trying to figure out the issue for days. This helped me immensely.
Thank you that worked for me! and saved me god knows how many hours
Great to hear that, @jamiecureton2898 ! 😀
Excellent !! Will always use this 🙂
Excel would not read converted bank statement dates e.g. 23Mar.......i used your suggestion and voila , in a matter of seconds i could change the entire column of unreadable dates and also back date to the year 2019. thank you!
Great instructional video. We can use the free Office Online Excel to accomplish this, too.
Love it! Thank you. Also subscribed 🙂
Thanks for subbing! 😀
Flash fill is a time saver...thanks for the video!
Thanks a lot for this :)
You're welcome, Moad! 😀
Super solution! Thanks Jon :-)
Thanks, Good learning
Thank you so much!
Hi, could you help me? When I tried to follow the steps, Excel did not do what you showed in the video, instead it gives me an error saying "Please double check the FLash Fill results. We encountered some input cells with values longer then 255 characters, and we had to skip them. "
I am trying extract the date from data like "Thu, 01 Aug 2024 20:48:52" to any reconginzable date format (e.g. like '2024-01-08' or '08/01/2024')
Sir John,
Just terrific! ; )
Flash fill worth the video alone thanks
What about the TimeZone and/or Daylight Savings Indicatior? shouldn't there be a new Column or Columns for each: "TimeZone" and Daylight Savings Y/N" ???
Or select the entire row/Format cells/Custom and choose and/or fill out the items you want to show in the cells.
Hi There, many thanks for the excellent description. I am learning excel through your tutorial . I have question here if we want to do any automation how can it be done?
To begin with any small example.
I have query , there is dataset includes start date and end date of multiple passengers. I need to find unique total number of travel days of the passenger and this is continuous process .
Thank you big help
Happy to help, @sparkvibe1319 😀
Thanks alot ❤
You're welcome 😊
Format cells - custom - fill out or remove the items you want or dont want
How will change the mixed data which have the date and month places are in different places
I would use = mid or left to separate the date
Hellow, sir can i ask questions? What if the transaction date/time is
Example: 2024-01-02 00:15:05.900?
I can't change it to, Jan-1-2024
Hi Saint! 😊 Try right-clicking the cell, and choosing Format Cells. In the Format Cells window that appears, select the Number tab at the top, then choose Custom as the Category. In the Type textbox, enter mmm-d-yyyy, then click OK. I hope that helps you! 😊
When I type the date and enter in the another column it remain still as text not changing for me
thanks man
I would have just taken the initial data and used "Text to Columns", selecting "Fixed Width", and then moving the lines to collate the data together as you see fit.
How to make daily weekly and monthly expenses sheet in excel
7:39
almost all doesnt work for me...how do you achive it?
Mar 14,2017 excel doent recognise automatically as a date as a result your video doensnt work
Your a cool dude
Not a terribly elegant solution, but it works.
=DATE(RIGHT(A2,4),VLOOKUP(MID(A2,5,3),{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,0),MID(A2,9,2)+0)+MID(A2,12,8)+0
Better version, based upon Tom Urtis pointer on Twitter [Using Month(1&"Jan") to convert to month number]:
=DATE(RIGHT(A2,4),MONTH(1&MID(A2,5,3)),MID(A2,9,2)+0)+MID(A2,12,8)+0
Because it's only 2017 and now is 2017 then this formula works:
=MID(A2,5,15)+0
Are you ben dyers uncle????
Wow
I already know
Are you Ben dyers uncle???