Attached a workbook of what I see. On column A and B there are dates with time on a "m/dd/yyyy hh:mm:ss" format.
Until a while ago excel 2010 would recognize the dates and I was able to find the time difference between the 2 just by typing =B1-A1 (column C)
I think due to a Microsoft update (?) I cannot do it anymore and excel will not recognize the info?
Would you know how I can fix this?
Thanks
This is because your dates are in text format and you need to covert them to real dates. To do that highlight cells and column A, go to Text to Columns, Delimited, Next, Next, select Date and finish. Do the same for column B.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
It will only work if I insert 2 new columns next to A and B and then separate by "Space" on Text to Columns. I need to keep the date and time next to each other however and not in separate columns.
Is there a way to do that?
No, it will keep data in place
I just did it. See attached file.
How did you do that?? I select column A, go to Text to Columns, Delimited, Next, Tab, Next, Date ("MDY"), Finish.
It will not work
After you have inserted the two columns and done the Text To Columns, you should have a "correct" date in column A and a time in column B. In column C enter =A1+B1 This will bring back the date and time as a real date and time. Now select column C and COPY then PASTE VALUES. This will eliminate the formula and keep the date and time.
Repeat for the other column of text dates
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I selected just the dates in the column. Delimited usually is a default. Next, Next and select Date and try maybe different format. MDY worked for me.
If everything else fails, try this
=--A1
Format cell as Custom, dd/m/yyy h:mm:ss
Doesn't work for me...
Maybe this will help you. I tried to show the process step by step.
Thanks for the reply newdoverman. This provides a solution however I will try to find another way because creating new columns (even if I delete them later) is a problem for me
You could copy the problem columns to the far right, go through the procedure to correct the date and times then copy them back over the columns in question. That way you wouldn't have to disturb your original data.
It's likely to do with US vs other parts of the world date format and what time zone your PC is set to.
13/01/2014 10:29:10 AM
1/13/2014 10:29:10 AM
Why an app as sophisticated as Excel can't auto recognise these or at least prompt for correct format is beyond me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks