- Alpine365Asked on June 05, 2015 at 09:55 AM
OK, the problem is this - I have several date fields on my form, formatted EU dates (dd/mm/yyyy)
The problem is, if users enter the year as two digits (eg 71, not 1971) the data is accepted/submitted, but then, when it is exported through Excel, it loses the date and becomes a large negative number eg -667726 (I assume this is 667726 days before a certain date (today?) meaning it treats the date 71 as AD0071? But as Excel does not have an date functionality before 01/01/1900 you cannot use this info to work back the correct date!
I have tried several fixes without success:
- changing the "limit dates" property (has no effect, users can enter any date they want. even if they use a 4 digit date)
- adding conditions for the date field - these worked to stop incorrect dates outside of the condition, but DIDN'T work for years entered as 2 digits!
- (edit - see below, this now works) setting up a text box with an input mask of ##/##/#### and clicking 'Fill Mask' - for some reason this does not work on my form, users do NOT need to enter 4 digits. BUT when I tried this on a test from it worked fine! - see "Test Date Mask" on the linked form
Can anyone help?
FYI I have just cleared my cache, and now the text box mask works, so that is something, but this is not actually a date, so I need to do some reformatting in Excel. And still can't find a way to get the date fields to not accept two digit years
- JotForm SupportWelvinAnswered on June 05, 2015 at 02:03 PM
Our apologies for the problem. I have tested this and was able to replicate the problem. Indeed, the result is causing a problem in the excel file.
I have now submitted this thread to our developers, requesting not to allow two digits in the year input for the Datetime field.
For now, you may consider using the following date widgets:
Thank you for reporting.