Appnitro Software Forums » MachForm 2

excel does not identify field in correct date format after import

Started 3 years ago by raj000 | 12 posts |


  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 3 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 3 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 3 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 3 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 3 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 3 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 3 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 3 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 3 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 3 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 3 years ago #

  12. beltex63
    Member

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

    Posted 1 year ago #

RSS feed for this topic

Reply