Sources: Excel Date formatting?

schergr · March 19, 2012 11:29AM

I have a spreadsheet with dates formatted as 3/14/2011. However when they come into Omniscope, they are recognized as Date/Time, but they render as 3/14/0011

If I change the date formatting in Excel, I get 3/14/11, but I do want the full four digit year. Am I doing something wrong?

hgross · March 19, 2012 11:54AM

An easy fix is to convert the date output using a field organizer as M/d/20yy

chris · March 19, 2012 12:06PM

Hi,

I can't reproduce this problem. Would it be possible to post an example spreadsheet?

indranildatta · March 20, 2012 5:40AM

I have often encountered this issue.

The easiest solution I've found is to simply change the Excel reader in the Data Source block from "Excel as text" to "Excel direct" or "Omniscope reader" and then specify the desired output date format in a Field Organiser block downstream.

There are of course performance trade-offs that the latter two reader are slower but I haven't noticed any significant difference in performance unless the file has millions and millions of rows.

I hope this helps.
Cheers!
Indranil

steve · March 20, 2012 6:04AM

Another solution is to turn off "parsing" of date fields in the file source block configuration, then use Field Organiser to parse the text value "3/14/11" using "M/d/yy". This will use the 2-to-4-digit conversion rule of 80 years in the past or 20 years in the future.

alec_webliquid · October 25, 2012 7:28AM

FYI, the same issue occurs when using 'Excel as Text' to read numerical values. The value is only read to the number of decimal places it is shown in the file, not the actual value. Obviously this can be disastrous - switching to 'Excel Direct' seems to solve it though :)

steve · October 26, 2012 4:41AM

Did you try "Omniscope reader"?

Welcome!

Categories

Ideas Parade

Tagged