Maryann Wood
asked on
Excel - data downlaoded to Excel, unable to change date time format
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you Rob. That worked perfectly! Happy Holidays.
ASKER
Thanks!
Holidays haven't started in the UK yet!! Then again, nor has the snow!
Thanks
Rob
Thanks
Rob
>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
@Rob: Nice - I've never thought of doing this for in-place conversion of a single column
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.
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.
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)&"/"&D
once done then you may wish to delete the data column that has times