I’ve created a quick screen-capture to show you how Excel dates filter when you manually enter them in Excel (or copy/paste) vs when you export them from GridPHP.

View post on imgur.com

Thanks

Mike

I’ve just modified the lib code to make all formatter=date columns to excel date format.

https://gist.github.com/gridphp/3b4484898043ca7725bdcc98eb48f00f

(line 5-24) to be added in main lib (jqgrid_dist.php)

To modify, search “if array changes, update it” comment and append the code as in gist.

That code is similar to attempts I tried, but unfortunately the dates either come-up as 1970-01-01 or FALSE. It does not export an actual date.

I tried to test everything before reaching-out, but I literally stalled trying to figure-out the logic of PHP Date Serial and Excel Date Serial.

I blame Microsoft, but the report users are blaming me (ha ha).

LAST_ORDER_DATE

1970-01-01

FALSE

FALSE

FALSE

FALSE

FALSE

I’ve used column options as follows. Try changing srcformat and newformat as below.

$col = array();
$col["title"] = "Date";
$col["name"] = "invdate"; 
$col["width"] = "50";
$col["editable"] = true; // this column is editable
$col["editoptions"] = array("size"=>20); // with default display of textbox with size 20
$col["editrules"] = array("required"=>true); // and is required
$col["formatter"] = "date"; // format as date
$col["formatoptions"] = array("srcformat"=>'Y-m-d',"newformat"=>'d/m/Y'); // format as date
$col["search"] = false;
$cols[] = $col;

Thanks for the update, I think I fixed the 1970 issue, it was likely my fault for having some of my testing code still in-place, I went back to stock settings.

I get the first date now, but each additional row just shows “false”.

As well, is still does-not open as a Date in Excel. I really think this could be a Microsoft glitch – but it’s so frustrating, as exporting to CSV works, but most of my reports lose something when exporting to CSV (such as leading zeros).

Thanks

Mike

LAST_ORDER_DATE

28/06/2013

FALSE

FALSE

FALSE

FALSE

FALSE

FALSE

FALSE

I’m sending you my demo code that works with online version of excel.
(I don’t have locally installed due to licensing)

You can test this code on the demo database that comes with the package.

Demo Code here: https://gist.github.com/gridphp/ca2e8796ec680c63f206e832634625e9

It’s encouraging you can get it to work with Excel Online.

I still can’t get all the dates to export – I only get FALSE for everything past the first date.

I think the array counter is not working in my case . . .

for($r=1;$r<count($arr);$r++)

I tried this:

for($r=1;$r<count($arr[0]);$r++)

But then it skips a few.

Is there a chance my jqgrid.dist is out of date compared to yours?

I’m running build 20210819-1130

Thanks

Mike

Please try running the demo code and see if it works at your end.

I’m also sending you latest build in direct email.