When you create a Form there are a lot of options on how to validate or format your inputs. For example, if you only want dates to be accepted in a form field in 'mm/dd/yyyy' format, you can require this by setting the Data Type, Data Validation and accepted formats.
You can access all these options when you create a field on the 'Edit Form Field' page.
Now if you go to enter that data in a form, the system will auto update your input to match the formatting. For example, 1/1/2019 will update to 01/01/2019 because you set "Data Formatting" to 'mm/dd/yyyy' format.
Why does my report data look wrong?
When you create a form field you have the option to set 'Data Type', 'Data Validation' and 'Data Formatting'. However, if you just select 'Data Type' and 'Data Validation' the system doesn't know what format the data should be when you pull the report. So if you have just Date Validations applied and no formatting, when you pull the report instead of reading 1/1/2019 in excel, it will read 43466.
This is because for each day since 01 January 1900, Excel adds one. So if you input 1/1/2019 and don't tell excel how to read it, it reads the data in 'General' format. You need to specify the "Data Formatting" so excel can read the report as intended.
How do I fix it?
To specify how data shows when you pull a report into excel, you have 2 options:
1. Edit the field formatting.
Go to the 'Edit Field' page for the field in question and set the 'Data Formatting' option.
2. Edit the report formating
To edit the report formatting, go to the report you want to want to edit and open the builder.
Then select the gear icon located next to the field you want to edit.
Then choose your "Formatting" from the drop down menu.
Now when you go to pull the report, excel will know how to read the data.
