I have an application that generates CSV info. It reports dates in the form "DD MM YYYY". For example "11 23 2021". This is a change in the application. It used to report dates as "11/23/2021" which was easy to import into my spreadsheet. it now imports as text and I cannot treat it as a date.
How can I either get Excel to treat it as a date or convert it to a date Excel can work with?
If the date is in cell A2, tryand format as date.Formula:
=SUBSTITUTE(A2," ","/")+0
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
You might even get away with a global replace of space by forward slash (can't test at the moment).
No formulas
Data-->Text to Columns-->Next-->Next-->Date-->DMY-->Finish
Getting close, I think, but that results in 3 separate cells, side by side. I want to change "11 23 2023" to "11/23/2023".
See post #2.
Or select the column > Find & Replace.
Find: " " (space without the quote marks - just hit the spacebar!
Replace: /
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Post #3, maybe?Originally Posted by AliGW
Completely missed that, Trevor - sorry!
Yes, I have been negligent.
I couldn't figure out why none of the suggestions were working out! I finally realized it was due to my mistake. The mistake is, I got confused about the actual format, which expresses the date as "Nov 21 2023" NOT "11 21 2023". The 11 21 2023 was from an experiment I was using while trying to work things out! Once I plugged the actual data into my REAL scenario, things no longer worked.
SO my REAL problem is converting the text "Nov 21 2023" to a date useable in an Excel formula! I have a string of dates that I want to average out. I know, it sounds like a dumb idea, but it works for me and has been working until my source changed its format. It has to do with making a plot of a data points over time and sometimes only having start and end times for some data points.
In "real life" I need to deal both with several-day spans and just a few-minute spans. In the long run, there are date+time timestamps being plotted (not just dates). I have other ways to deal with this, like taking min or max instead of average, or making multiple plots (both min and max) but for now a simple average suffices.
Sorry for my stumbling around!
Last edited by bertilak45; 11-01-2023 at 12:39 PM.
@Ali: no worries
See file with "Custom Format"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks