https://www.machform.com/forums/ MachForm Community Forums Topic: excel does not identify field in correct date format after import en Mon, 04 May 2026 05:48:00 +0000 https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-11440 Thu, 17 Feb 2011 03:19:08 +0000 beltex63 11440@https://www.machform.com/forums/ <p>That is so "Barney Rubble". There should be a fix for this. </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4948 Sat, 07 Mar 2009 16:20:12 +0000 raj000 4948@https://www.machform.com/forums/ <p>for others that may be interested, i managed to complete this task through a single click by recording an excel macro. </p> <p>The macro refreshes the data as well as alligns the date column through Tools &gt;&gt; Text To Column &gt;&gt; finish .. all in one click. </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4601 Tue, 10 Feb 2009 14:31:36 +0000 raj000 4601@https://www.machform.com/forums/ <p>This doesn't seem to be working. I have tried all combinations. Is this problem only at my end .. ? Is excel recognizing the date in true date format at your end? </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4535 Thu, 05 Feb 2009 13:39:01 +0000 redityo 4535@https://www.machform.com/forums/ <p>Hi,</p> <p>I think you can set the format to mm/dd/yyyy, you see in step 1 and 2 there are these code :</p> <pre><code>$dateFormat-&gt;setNumFormat(&#39;yyyy/mm/dd&#39;); $form_data[$i][$j] = date(&quot;Y/m/d&quot;,strtotime($row[$column_name]));</code></pre> <p>you can change those format to</p> <pre><code>$dateFormat-&gt;setNumFormat(&#39;mm/dd/yyyy&#39;); $form_data[$i][$j] = date(&quot;m/d/Y&quot;,strtotime($row[$column_name]));</code></pre> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4528 Wed, 04 Feb 2009 12:44:03 +0000 raj000 4528@https://www.machform.com/forums/ <p>yes. i checked the cell properties and the the custom date form is set to "yyyy/mm/dd".</p> <p>it still gets left aligned. my default system date is set to UK format which is mm/dd/yyyy . Could the default system date setting cause a problem? </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4510 Mon, 02 Feb 2009 16:38:38 +0000 redityo 4510@https://www.machform.com/forums/ <p>Hi,</p> <p>Have you try to check in cell properties ? if you look at the properties it show custom format date with "yyyy/mm/dd" . </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4508 Mon, 02 Feb 2009 15:01:53 +0000 raj000 4508@https://www.machform.com/forums/ <p>Hi,</p> <p>I made all the changes to the export_entries.php . Then i downloaded the latest excel file and imported it again.</p> <p>Nothing seems to have changed. The cells are still being understood as text, not date.</p> <p>Am i supposed to make any modifications to my excel file? </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4498 Sun, 01 Feb 2009 08:43:59 +0000 redityo 4498@https://www.machform.com/forums/ <p>Hi,</p> <p>Maybe you can try to add date format in excel cell, to do so edit you should edit your "export_entries.php" and do this steps :</p> <p>1. Goto line 161 you will find this code :</p> <pre><code>$format_bold-&gt;setBorder(1);</code></pre> <p>add exactly bellow that code</p> <pre><code>$dateFormat =&#38; $workbook-&gt;addFormat(); $dateFormat-&gt;setNumFormat(&#39;yyyy/mm/dd&#39;);</code></pre> <p>2. Goto around line 289 ~ 292, replace these code from :</p> <pre><code>if(!empty($row[$column_name]) &#38;&#38; ($row[$column_name] != &#39;0000-00-00&#39;)){ $form_data[$i][$j] = date(&quot;Y/m/d&quot;,strtotime($row[$column_name])); }</code></pre> <p>to </p> <pre><code>if(!empty($row[$column_name]) &#38;&#38; ($row[$column_name] != &#39;0000-00-00&#39;)){ $form_data[$i][$j] = date(&quot;Y/m/d&quot;,strtotime($row[$column_name])); } $cell_date = $j;</code></pre> <p>3. Goto line 360, replace this code from :</p> <pre><code>$worksheet-&gt;write($row_num, $col_num, $data);</code></pre> <pre><code>if ($cell_date == $col_num) { $worksheet-&gt;write($row_num, $col_num, $data ,$dateFormat); } else { $worksheet-&gt;write($row_num, $col_num, $data); }</code></pre> <p>if you look at the cell format properties (right click the cell), it will show custom date format with "yyyy/mm/dd". Maybe this url will help you :</p> <p><a href="http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-format.setnumformat.php" rel="nofollow">http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-format.setnumformat.php</a></p> <p>and</p> <p><a href="http://bugs.pear.php.net/bugs/bug.php?id=3832" rel="nofollow">http://bugs.pear.php.net/bugs/bug.php?id=3832</a> </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4489 Fri, 30 Jan 2009 20:14:21 +0000 raj000 4489@https://www.machform.com/forums/ <p>that seems to have worked. however, every time i refresh my excel data with the new entries in machform, i have to follow the same steps again. </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4461 Thu, 29 Jan 2009 10:56:27 +0000 yuniar 4461@https://www.machform.com/forums/ <p>You need to do some column data formatting within your Excel for this.<br /> I found an answer by Microsoft Excel MVP:<br /> <a href="http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2008-11/msg06901.html" rel="nofollow">http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2008-11/msg06901.html</a></p> <pre><code>When a cell is formatted as one type and you change that format type by going to the menu Format&gt;Cells, the new format isn&#39;t applied until you edit the cell. That&#39;s what you&#39;re doing when you double click the cell. Try this... Select the range of cells that hold the text dates. Goto the menu Data&gt;Text to Columns Click Finish This will usually reset the format to General and then Excel will recognize the dates as true Excel dates then automatcally set the format to date.</code></pre> <p>So, play around with the "Data-&gt;Text to Columns" menu. Apply it to your date column and make sure to choose the "column data format" as date. </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4451 Wed, 28 Jan 2009 15:46:31 +0000 raj000 4451@https://www.machform.com/forums/ <p>hi, any ideas on this . i still cant open the downloaded excel file and get excel to read the date column in a date format. It still identifies it as a text field.</p> <p>thanks </p> https://www.machform.com/forums/topic/excel-does-not-identify-field-in-correct-date-format-after-import#post-4162 Wed, 07 Jan 2009 16:50:40 +0000 raj000 4162@https://www.machform.com/forums/ <p>I have a form with a date field and some other fields.</p> <p>When I download entries of this form as an excel file and open it in excel, it identifies the date field as a text field. </p> <p>However, if i select a date record by double clicking the cell and simply hit enter, it correctly right alligns the value. In excel this means the date format has been correctly identified. It would be a nightmare to do this for all records each time.</p> <p>I tried to change the date format of my excel as well as the region settings in windows...but it didnt work.</p> <p>Any ideas..thanks</p> <p>:) </p>