-1

I am simply importing CSV into Excel, In which I have a date column. it seem like this.

10.22.2014 13:34:00

When I am finish Importing Now I want to convet the whole date column to look like this in the format cell section but it is not working for me. Can you suggest another way. What can be the main reason SUppose i put a formula on the column then Everytime user import the data he need formula which might be not a good idea, Is there something I can do when I m importing or just like wondering what could be done?

10/22/2014 1:34:00 AM or PM

sikandar bakht syed
  • 323
  • 1
  • 4
  • 9
  • Possible duplicate: http://stackoverflow.com/questions/21297207/how-to-convert-text-to-date-format-in-excel – ako Nov 11 '14 at 23:51
  • @ Ako Nope its not the same I have already tried that solution but that didnt worked for me, I tried changing in the formula but no help, I tired to add comment and that need reputation of 50 so That is why i put this question. If you have solution please post it. – sikandar bakht syed Nov 12 '14 at 07:09
  • Saying what goes wrong provides more info than just "didn't work for me", but I put one approach below. – ako Nov 13 '14 at 08:27
  • 1
    I noticed you asked separate, very similar question a few days earlier http://stackoverflow.com/questions/26826567/excel-date-time-conversion. Folks are not going to want to help you much if they get the perception you are wasting their time. – ako Nov 17 '14 at 20:26

1 Answers1

1

Here's one approach:

  1. Substitute dots with slashes
  2. Use DATEVALUE, TIMEVALUE functions on relevant subsections of this substituted string. Subsections are fetched using LEFT and RIGHT string functions.
    1. These return a serial number for dates (days since 1900) and time (a floating point between 0 and 1). When summed, the value can be represented as both date and time, in a format of your choice, as shown below:

enter image description here

Or, showing formulas:

enter image description here

EDIT: adding all-in-one formula. Note that this will give you the serial value (41934.5652777778), which can then be formated using the built in formats for dates / times--just select the one you want. This does not actually render a string:

=DATEVALUE(LEFT(SUBSTITUTE(A1,".","/"),10))+TIMEVALUE(RIGHT(SUBSTITUTE(A1,".","/"),8))

enter image description here

If, however, you do want a string returned, you can use the TEXT function.

=TEXT(DATEVALUE(LEFT(SUBSTITUTE(A1,".","/"),10))+TIMEVALUE(RIGHT(SUBSTITUTE(A1,".","/"),8)),"m/d/yyyy h:mm AM/PM")

(This is done in libreoffice, but the same formulas and arguments exist in MS Excel)

ako
  • 3,569
  • 4
  • 27
  • 38
  • you have provided a good solution but can you make it in one shot rather than first splitting and then summing up ? because As I see 4 column will be used to get the final value so in stead can we sum up in just once column? Once again thanks I appreciate it. – sikandar bakht syed Nov 13 '14 at 08:48
  • I have come with this solution adding your subsitute as well `=SUBSTITUTE(LEFT(A3,11)&IF(MOD(VALUE(MID(A3,12,16)),12)=0,12,MOD(VALUE(MID(A3,12,1)),12)&IF(VALUE(MID(A3,12,1))<12," AM"," PM")),".","/")` as a result I get this **10/22/2014 1 AM ** Now what i need is to get the minute as well like **10/22/2014 1:00 AM** – sikandar bakht syed Nov 13 '14 at 08:59
  • not sure why you need the conditionals. I updated the answer above. – ako Nov 13 '14 at 21:08
  • Did this work? Then consider closing the question down by approving the answer. – ako Nov 14 '14 at 22:29
  • yeah its working now but can you help now how can I put a code if I want to convert it from ACCESS VBA i.e accessing the excel file and write a code to convert it, for me just as above you did ? – sikandar bakht syed Nov 15 '14 at 18:52
  • not sure I follow your workflow and what is done in Access and what is done in Excel, and the role of VBA in your workflow so hard to comment--anyway, sounds like a new question that is beyond what you asked here? If so, wrap this one up and post a new one, with adequate detail on the new problem. – ako Nov 15 '14 at 19:39