Solved

Excel - data downlaoded to Excel, unable to change date time format

Posted on 2014-12-03
8
331 Views
Last Modified: 2014-12-03
We downloaded data regularly from our CMMS software, Maximo, to Excel.  However, once in Excel, we are unable to select a date/time column and format cells.  

Example -
Date/time comes across as 2/22/2014 12:00 a.m.
Change:  Format Cells - Date - 2/22/2014

I can change the column format and then go into the individual cell and when I type the date it converts - but does not auto convert all the date/times?

Thank you.
0
Comment
Question by:washvt
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40478844
Sounds like the dates are coming in as text. Entering the cell and pressing enter is forcing Excel to re-assess the contents of the cell and will format accordingly.

You can use the Text to Columns function to do this on a range.

Thanks
Rob
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40478850
assuming your data  starts in a2 put this formula in the next column and drag down

you will get the result in the serial number then you change the format to dates and then it will only show dates without any time in it.

=DATEVALUE(MONTH(A2)&"/"&DAY(A2)&"/"&YEAR(A2))

once done then you may wish to delete the data column that has times
0
 
LVL 18

Expert Comment

by:Simon
ID: 40478854
I don't know Maximo, but if it comes in as a delimited file, the first place to catch it is in the delmited file import wizard. Set the data type of that column to date.

If that doesn't work and still have text in hte original format, use the =datevalue(A1) function to get the date and then format the cell with the formula in to whatever date format you like.
0
 

Author Comment

by:washvt
ID: 40478855
Thank you Rob. That worked perfectly!  Happy Holidays.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:washvt
ID: 40478857
Thanks!
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40478861
Holidays haven't started in the UK yet!! Then again, nor has the snow!

Thanks
Rob
0
 
LVL 18

Expert Comment

by:Simon
ID: 40478869
>You can use the Text to Columns function to do this on a range.
@Rob: Nice - I've never thought of doing this for in-place conversion of a single column
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40478872
Something I picked up on EE, don't recall which Expert though.

Works well when all values are text. Not so well when download is in numbers but Excel doesn't recognise the values as dates eg 14/12/2014, to me that is 14th December but if it were in US format 12/14/2014 my Excel wouldn't recognise it.

Selecting the whole range and using text to columns could mess up those that were recognised.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now