Back to limesurvey.org

Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Export to Excel or PDF or CVS Date format is wrong

More
9 years 3 months ago #137050 by teksur
Replied by teksur on topic Export to Excel or PDF or CVS Date format is wrong

holch wrote: Especially as you can correct this fairly easy in Excel.

Could you share me this easy way pls?
I tried to change format in cell and that unfortunately did not work.
The topic has been locked.
More
9 years 3 months ago #137062 by holch
Replied by holch on topic Export to Excel or PDF or CVS Date format is wrong
Well, depending on the number of response I would either look for a formula on how to convert 2012-02-14 into 14.02.2012.

But if the number of entries and the time range isn't too big, I would sort the table regarding date and then copy the correct date to all from the same day. If you have a survey going on for many days, then this is of course quite tedious. Then I would look into formulas in Excel to convert this. If you want to go the formula way, this could be a start. Someone is trying to do the exact oposite of you, but it should give you some idea on how to proceed.

superuser.com/questions/793137/how-to-co...-mm-dd-in-excel-2007
Help us to help you!
  • Provide your LS version and where it is installed (own server, uni/employer, SaaS hosting, etc.).
  • Always provide a LSS file (not LSQ or LSG).
Note: I answer at this forum in my spare time, I'm not a LimeSurvey GmbH employee.
The topic has been locked.
More
9 years 3 months ago #137087 by teksur
Replied by teksur on topic Export to Excel or PDF or CVS Date format is wrong

holch wrote: Well, depending on the number of response I would either look for a formula on how to convert 2012-02-14 into 14.02.2012.

But if the number of entries and the time range isn't too big, I would sort the table regarding date and then copy the correct date to all from the same day. If you have a survey going on for many days, then this is of course quite tedious. Then I would look into formulas in Excel to convert this. If you want to go the formula way, this could be a start. Someone is trying to do the exact oposite of you, but it should give you some idea on how to proceed.

superuser.com/questions/793137/how-to-co...-mm-dd-in-excel-2007


Thank you for these, but I don't think this is good idea to work this in Excel. This survey what I am working now will last for years and there are birthdays and similar things. I don't need any calculation for them. I am using this as database form and this dump to Excel is very important, because rest of work will be done in Excel. This form has more than 100 questions.

In datebase date is in right format for me dd.mm.yyyy so in export you have code that will change that to yyyy-mm-dd and add time 00:00:00(I don't know why, not needed at all)
From my perspective it has too much PHP for me :)
So little tick in Export don't change my date to yyyy-mm-dd format would be great! :)
I don't know PHP, but I have hunch that it would not need so many if and else commands from good PHP coder like you :laugh:
I only see one other option that I would have to loose this date picker component and use normal textfield where they have to enter date in dd.mm.yyyy format.

What do you think?
The topic has been locked.
More
9 years 3 months ago #137088 by DenisChenu
Replied by DenisChenu on topic Export to Excel or PDF or CVS Date format is wrong

teksur wrote: In datebase date is in right format for me dd.mm.yyyy so in export you have code that will change that to yyyy-mm-dd and add time 00:00:00(I don't know why, not needed at all)

DateTime format in SQL : always Y-M-D h:m:s

ANd if you import a 01/02/03 in excel in french or in english : date is different. Using same format , always : you are sure firt is year, seond i month and last is day of month.

Denis
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member. - Professional support - Plugins, theme and development .
I don't answer to private message.
The topic has been locked.
More
9 years 3 months ago #137093 by teksur
Replied by teksur on topic Export to Excel or PDF or CVS Date format is wrong

DenisChenu wrote:

teksur wrote: In datebase date is in right format for me dd.mm.yyyy so in export you have code that will change that to yyyy-mm-dd and add time 00:00:00(I don't know why, not needed at all)

DateTime format in SQL : always Y-M-D h:m:s

ANd if you import a 01/02/03 in excel in french or in english : date is different. Using same format , always : you are sure firt is year, seond i month and last is day of month.

Denis

I was little confused, because you said earlier that "Because : we always use english date format when exporting." You are right it is in database this yyyy-mm-dd format. But you are able to show this in in my format dd.mm.yyyy in Browse responses -> Display responses. Everything is fine for me in there. Why I can't have this to Excel?
The topic has been locked.
More
9 years 3 months ago #137109 by holch
Replied by holch on topic Export to Excel or PDF or CVS Date format is wrong
One thing is how things are displayed (just a reformatting), the other aspect is how it is saved in the database.

I am not a real programmer myself, but I know a few things. And it is never that simple as it looks. When you make those supposed "minor" changes, there can be a lot of impact. For example: EM can use date to make calculations. If the format written into the database is not always the same, EM needs to identify the format and be able to work with it. That is a big deal.

There is probably some way to read the date out of your question, reformat it and write it into an equation type question, but this is out of my league.
Help us to help you!
  • Provide your LS version and where it is installed (own server, uni/employer, SaaS hosting, etc.).
  • Always provide a LSS file (not LSQ or LSG).
Note: I answer at this forum in my spare time, I'm not a LimeSurvey GmbH employee.
The topic has been locked.
More
9 years 3 months ago #137117 by teksur
Replied by teksur on topic Export to Excel or PDF or CVS Date format is wrong
Well, way I see it answer is already here. Someone just need put these 2 codes(Export Excel and Display responses) working together and then we have it :)
The topic has been locked.
More
9 years 3 months ago #137118 by holch
Replied by holch on topic Export to Excel or PDF or CVS Date format is wrong
How about sponsoring the feature? As I said, I don't see that anyone will take this over any time soon.
Help us to help you!
  • Provide your LS version and where it is installed (own server, uni/employer, SaaS hosting, etc.).
  • Always provide a LSS file (not LSQ or LSG).
Note: I answer at this forum in my spare time, I'm not a LimeSurvey GmbH employee.
The topic has been locked.
More
9 years 3 months ago #137119 by teksur
Replied by teksur on topic Export to Excel or PDF or CVS Date format is wrong

holch wrote: How about sponsoring the feature? As I said, I don't see that anyone will take this over any time soon.

What do you mean by sponsoring?
I am going to by premium package 12 months if I get this working somehow.
Someone said I could do plugin myself.
The topic has been locked.
More
9 years 3 months ago #137127 by holch
Replied by holch on topic Export to Excel or PDF or CVS Date format is wrong
Well, I don't know what the premium package includes (this is from the Limesurvey GmbH), but from what I know it is the comfort update at the moment, which is well worth its money.

The rest is based on Limesurey's current features. If you require different features there are a few options:
  • - Make a feature request (which I think you already did) and wait for its implementation. However, just like with other software this might happen, or not. Even if you spend thousands of dollars/euros on commercial survey packages this is not guaranteed. Back in the day when we used a very expensive survey tool we had tons of feature requests, some made it in the package, others didn't.
  • Sponsoring: Find a developer that will implement this into Limesurvey, if possible. Of course they probably won't do this for free.
  • Implement it as a plugin (if possible).
Help us to help you!
  • Provide your LS version and where it is installed (own server, uni/employer, SaaS hosting, etc.).
  • Always provide a LSS file (not LSQ or LSG).
Note: I answer at this forum in my spare time, I'm not a LimeSurvey GmbH employee.
The following user(s) said Thank You: DenisChenu
The topic has been locked.
More
9 years 3 months ago #137128 by teksur
Replied by teksur on topic Export to Excel or PDF or CVS Date format is wrong

holch wrote: Well, I don't know what the premium package includes (this is from the Limesurvey GmbH), but from what I know it is the comfort update at the moment, which is well worth its money.

The rest is based on Limesurey's current features. If you require different features there are a few options:

  • - Make a feature request (which I think you already did) and wait for its implementation. However, just like with other software this might happen, or not. Even if you spend thousands of dollars/euros on commercial survey packages this is not guaranteed. Back in the day when we used a very expensive survey tool we had tons of feature requests, some made it in the package, others didn't.
  • Sponsoring: Find a developer that will implement this into Limesurvey, if possible. Of course they probably won't do this for free.
  • Implement it as a plugin (if possible).


I thank you for explaining to me these options. I have to think about them.
The topic has been locked.
More
9 years 1 month ago #139931 by Joffm
Replied by Joffm on topic Export to Excel or PDF or CVS Date format is wrong
Hi, teksur,
I know this topic may be outdated.
But I just want to explain a in my opinion short and easy way to transfer the database format "YYYY-MM-DD" to an EXCEL format (days since 1.1.1900 or whatever).
  1. Export your data as SPSS file (you only need the data file)
  2. Open this in EXCEL (delimiter is komma)
  3. Add a column after the date column (the date will look like '17-06-2016 00:00:00', maybe because of my German EXCEL)
  4. In the new column enter the formula "=RIGHT(LEFT(B1;11);10)*1"
  5. You will get the value 42528, the EXCEL representation of the date.
  6. This you can format to your need with the EXCEL functionality.

Kind regards
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
The topic has been locked.
More
7 years 11 months ago #159353 by Joffm
Replied by Joffm on topic Export to Excel or PDF or CVS Date format is wrong
Hello, emilyjohn,

why do you very frequently reply to absolutely outdated topics? And mostly insubstantial.

Here you just quoted a part of holch's answer 1 year 4 months ago.

What is the reason for that?

Regards
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
The following user(s) said Thank You: DenisChenu
The topic has been locked.
More
7 years 9 months ago #161413 by tomy137
Replied by tomy137 on topic Export to Excel or PDF or CVS Date format is wrong
Hi there,

This is an outdated topic but the associated Mantis likewise : bugs.limesurvey.org/view.php?id=11293

That's not a big deal, I don't understand why there is so few consideration about this question.

Maybe on this script (LimeSurvey/application/helpers/export_helper.php) :
Code:
  {
            $fieldno = strtoupper($field['sql_name']);
            if($field['SPSStype']=='DATETIME23.2') {
                // convert mysql  datestamp (yyyy-mm-dd hh:mm:ss) to SPSS datetime (dd-mmm-yyyy hh:mm:ss) format
                if (isset($row[$fieldno])) {
                    list( $year, $month, $day, $hour, $minute, $second ) = preg_split( '([^0-9])', $row[$fieldno] );
                    if ($year != '' && (int)$year >= 1900) {
                        echo $q.date('d-m-Y H:i:s', mktime( $hour, $minute, $second, $month, $day, $year ) ).$q;
                    } else {
                        echo ($na);
                    }
                }  else {
                    echo ($na);
                }
            }

Thanks in advance for your reply,
The topic has been locked.
  • LouisGac
  • LouisGac's Avatar
  • New Member
  • New Member
More
7 years 9 months ago #161414 by LouisGac
Replied by LouisGac on topic Export to Excel or PDF or CVS Date format is wrong
Hi tomy,

LimeSurvey is OpenSource, so if you're a PHP coder and you found a fix for a bug, feel free to submit a pull request to the gitHub repository:

github.com/LimeSurvey/LimeSurvey/blob/master/CONTRIBUTING.md
The topic has been locked.
Moderators: holch, tpartner

Lime-years ahead

Online-surveys for every purse and purpose

Pricing & Plans
Get started

Legal

About Us

Open Source