MachForm Community Forums » MachForm 2

1. raj000
   Member

   I have a form with a date field and some other fields.

   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.

   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.

   I tried to change the date format of my excel as well as the region settings in windows...but it didnt work.

   Any ideas..thanks

   :)

   Posted 15 years ago #

2. raj000
   Member

   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.

   thanks

   Posted 15 years ago #
3. 

   yuniar

   You need to do some column data formatting within your Excel for this.
   I found an answer by Microsoft Excel MVP:
   http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2008-11/msg06901.html

   When a cell is formatted as one type and you change that format type by
   going to the menu Format>Cells, the new format isn't applied until you edit
   the cell. That's what you're doing when you double click the cell.
   
   Try this...
   
   Select the range of cells that hold the text dates.
   Goto the menu Data>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.

   So, play around with the "Data->Text to Columns" menu. Apply it to your date column and make sure to choose the "column data format" as date.

   ---

   MachForm Founder

   Posted 15 years ago #

4. raj000
   Member

   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.

   Posted 15 years ago #
5. 

   redityo

   Hi,

   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 :

   1. Goto line 161 you will find this code :

   $format_bold->setBorder(1);

   add exactly bellow that code

   $dateFormat =& $workbook->addFormat();
   $dateFormat->setNumFormat('yyyy/mm/dd');

   2. Goto around line 289 ~ 292, replace these code from :

   if(!empty($row[$column_name]) && ($row[$column_name] != '0000-00-00')){
   	$form_data[$i][$j] = date("Y/m/d",strtotime($row[$column_name]));
   }

   to

   if(!empty($row[$column_name]) && ($row[$column_name] != '0000-00-00')){
   	$form_data[$i][$j] = date("Y/m/d",strtotime($row[$column_name]));
   }
   $cell_date = $j;

   3. Goto line 360, replace this code from :

   $worksheet->write($row_num, $col_num, $data);

   if ($cell_date == $col_num) {
   
   	$worksheet->write($row_num, $col_num, $data ,$dateFormat);
   }
   else {
   	$worksheet->write($row_num, $col_num, $data);
   }

   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 :

   http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-format.setnumformat.php

   and

   http://bugs.pear.php.net/bugs/bug.php?id=3832

   ---

   MachForm Support

   Posted 15 years ago #

6. raj000
   Member

   Hi,

   I made all the changes to the export_entries.php . Then i downloaded the latest excel file and imported it again.

   Nothing seems to have changed. The cells are still being understood as text, not date.

   Am i supposed to make any modifications to my excel file?

   Posted 15 years ago #
7. 

   redityo

   Hi,

   Have you try to check in cell properties ? if you look at the properties it show custom format date with "yyyy/mm/dd" .

   ---

   MachForm Support

   Posted 15 years ago #

8. raj000
   Member

   yes. i checked the cell properties and the the custom date form is set to "yyyy/mm/dd".

   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?

   Posted 15 years ago #
9. 

   redityo

   Hi,

   I think you can set the format to mm/dd/yyyy, you see in step 1 and 2 there are these code :

   $dateFormat->setNumFormat('yyyy/mm/dd');
   
   $form_data[$i][$j] = date("Y/m/d",strtotime($row[$column_name]));

   you can change those format to

   $dateFormat->setNumFormat('mm/dd/yyyy');
   
   $form_data[$i][$j] = date("m/d/Y",strtotime($row[$column_name]));

   ---

   MachForm Support

   Posted 15 years ago #

10. raj000
    Member

    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?

    Posted 15 years ago #

11. raj000
    Member

    for others that may be interested, i managed to complete this task through a single click by recording an excel macro.

    The macro refreshes the data as well as alligns the date column through Tools >> Text To Column >> finish .. all in one click.

    Posted 15 years ago #

12. beltex63
    Member

    That is so "Barney Rubble". There should be a fix for this.

    Posted 13 years ago #

RSS feed for this topic

Reply

You must log in to post.