Date/Time fields in Excel

4 posts / 0 new
Last post
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
Date/Time fields in Excel

Sometimes in Excel (and other databases) you will come across a field that contains both the date and time information such as: 2014-05-05  12:00:00 PM

If you bring in this field using the regular Excel import you will only capture the date portion of the field and the time portion gets dropped.

I was reading something on the IDEA forum and decided to try import using ODBC drivers for Excel, so I imported the same file and what happens is that in IDEA you will have now have two fields, one containing the date info and the other containing the time info.  So it seems if you have this type of problem you should be importing the file using the ODBC option and not the Excel option.

So does anyone else ever performed imports like this and have seen differences?

Lois's picture
Lois
Offline
Joined: 02/14/2013 - 09:00

Not yet but this is a helpful tip. Will keep this in my toolbox. Thanks Brian.

yinondotan
Offline
Joined: 11/20/2016 - 10:14

Hi Brian
I had the same problem recently .The solution I found was to save the excel file as .csv and after import to idea of the date time fields as text, to define two virtual fields one date and one time.
 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Yinon, thanks for the tip.