# Date incorrect parsing from Excel **URL:** \ **Category:** CloverDX Platform\ **Created:** [March 6, 2010, 12:00am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670 "2010-03-06T00:00:00Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![mkutti](https://avatars.discourse-cdn.com/v4/letter/m/a88e4f/32.png) [@mkutti](https://forum.cloverdx.com/u/mkutti)\ **Post date:** [March 6, 2010, 12:00am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/1 "2010-03-06T00:00:00Z")
When I read a Excel file using XLS component, Date value Aug 07 2009 changes to 0007, August 07, 2009. Metadata is defined as String. Is this a bug ? Any workaround ? Thank you. ---
**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\ **Post date:** [March 8, 2010, 8:19am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/2 "2010-03-08T08:19:25Z")
What happens when you define metadata as Date? ---
**Author:** ![mkutti](https://avatars.discourse-cdn.com/v4/letter/m/a88e4f/32.png) [@mkutti](https://forum.cloverdx.com/u/mkutti)\ **Post date:** [March 9, 2010, 3:42am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/3 "2010-03-09T03:42:40Z")
If I define metadata as Date, it reads the Date without additional value in the front. Strange - it reads the value as one day less than the date in Excel. 6/1/2009 becomes 5/31/2009. ---
**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\ **Post date:** [March 9, 2010, 7:27am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/4 "2010-03-09T07:27:31Z")
It’s really strange. Could you send me the file with data probe? ---
**Author:** ![mkutti](https://avatars.discourse-cdn.com/v4/letter/m/a88e4f/32.png) [@mkutti](https://forum.cloverdx.com/u/mkutti)\ **Post date:** [March 10, 2010, 8:23am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/5 "2010-03-10T08:23:32Z")
zip file with graph and excel is attached. Thanks. - [test1.zip](https://cdn.cloverdx.com/forum/11656%5Fb559778bba5ef9d48abbfa93ebe90499/test1.zip) ---
**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\ **Post date:** [March 10, 2010, 10:20am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/6 "2010-03-10T10:20:29Z")
Hello, reading the dates as a Date type problem, seems to be related to the time zone problem ([http://bug.cloveretl.com/view.php?id=3491](http://bug.cloveretl.com/view.php?id=3491)). That means, that reading timestamps from xls file depends on time zone settings on the computer - dates on excel file are always treated as formatted in GMT time. As a workaround I can advice you the usage of [Java Execute component](http://wiki.clovergui.net/doku.php?id=components:others#java%5Fexecute), that sets the default time zone to GMT time: ```auto import java.util.SimpleTimeZone; import java.util.TimeZone; import org.jetel.component.BasicJavaRunnable; public class setTimeZone extends BasicJavaRunnable { public void run() { TimeZone.setDefault(new SimpleTimeZone(0, "GMT")); } } ``` But the above, sets the GMT time for parsing and formatting all dates in your graph, so you can have a problem in different point of the transformation. When I set the metadata type to String, the data was read properly, without any additional strings. What Clover version do you use? ---
**Author:** ![mkutti](https://avatars.discourse-cdn.com/v4/letter/m/a88e4f/32.png) [@mkutti](https://forum.cloverdx.com/u/mkutti)\ **Post date:** [March 10, 2010, 8:50pm UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/7 "2010-03-10T20:50:48Z")
Hi, Please see the attached zip for recreating the issue. Right now I am proceeding with a workaround - substring function. Also, Is it possible to combine both the ExtFilter in one in the attached graph ? I tried combining isnull, nvl with is\_number. It does not help. Short circuit && is not available in CTL and I can’t convert the code to Java in ExtFilter. Not a big issue . I am managing with two filters. Thought you might suggest a better solution. Thanks!! - [test1.zip](https://cdn.cloverdx.com/forum/11656%5Ffb69707fd8a5dadae77790153216e5fe/test1.zip) ---
**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\ **Post date:** [March 11, 2010, 10:52am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/8 "2010-03-11T10:52:38Z")
Hello, _&&_ operator as well as _and_ works properly with Filter: ```auto !is_blank($0.is_int_test) and is_number($0.is_int_test) ``` or ```auto !is_blank($0.is_int_test) && is_number($0.is_int_test) ``` filters only 3 of 5 records. If you know all the date formats in excel file you can use _try\_convert_ or _str2date_ functions in Reformat node: ```auto //#TL date d; string format = "dddd, MMMM dd, yyyy"; // Transforms input record into output record. function transform() { d = try_convert($0.DateString,date, "MM/dd/yy"); if (isnull(d)){ d = str2date($0.DateString,format); } $0.* := $0.*; $0.Date := d; } ``` If you set Reformat with above code after XLSReader, you can also use it as a Filter: ```auto //#TL date d; string format = "dddd, MMMM dd, yyyy"; // Transforms input record into output record. function transform() { if (is_blank($0.is_int_test) || !is_number($0.is_int_test)) return SKIP; d = try_convert($0.DateString,date, "MM/dd/yy"); if (isnull(d)){ d = str2date($0.DateString,format); } $0.* := $0.*; $0.Date := d; } ``` ---
**Author:** ![Alex\_23](https://avatars.discourse-cdn.com/v4/letter/a/6a8cbe/32.png) [@Alex\_23](https://forum.cloverdx.com/u/Alex_23)\ **Post date:** [March 7, 2023, 10:15am UTC](https://forum.cloverdx.com/t/date-incorrect-parsing-from-excel/670/9 "2023-03-07T10:15:20Z")
I have solved my issue by applying filter to the whole column. [here](https://thegeekpage.com/excel-do-not-filter-correctly/)