Excel does not recognize the date format

Hasit Ruparel commented on Jun 18, 2020

The TimeBucket function won't let me group by month when the source is Excel, try CSV and it works.

KoolReport commented on Jun 19, 2020

Could you please check the format of the time, it could be in the standard form that TimeBucket could not automatically recognize and process.

David Winterburn commented on Jun 19, 2020

Hi Hasit,

Please post some sample of data from your Excel and CSV files and your code for TimeBucket process. Maybe we could solve this with "formatString" property. Thanks!

Hasit Ruparel commented on Jun 19, 2020

I have also sent the file via email to support@ for you to check.

Date format works fine in excel, I did a filter to verify.

Hasit Ruparel commented on Jun 19, 2020

How does the formatstring works?

David Winterburn commented on Jun 19, 2020

We haven't received your email yet. But I have a suggestion. Please set the your date column's "format" property like this:

$this->src("excel")
->pipe(new \koolreport\processes\ColumnMeta(array(
    "myDateColumn" => array(
        "format" => $formatString, //e.g: "Y-m-d H:i:s"
    )
)))
//pipe TimeBucket here

The format string depends on your date column's real data. Please check this link for the date code: https://www.php.net/manual/en/function.date.php

edwards commented on Nov 9, 2020

Well, there are several fixes to resolve this excel date not recognizing error.

1# Text To Columns 2# Dates As Text 3# Convert Date Into Numbers 4# Format The Dates 5# Use The VALUE Function 6# DATEVALUE Function 7# Find & Replace

For catching more detail on how to perform these fixes, check out this informative post.

KoolReport's Forum

Excel does not recognize the date format #1492

Build Your Excellent Data Report