When I export custom reports the date fields are in the form “02/21/2022 12:14:34 PM” obviously as mm/dd/yyyy hh:mm:ss PM
Is there any way that this can be shown in the local time zone format?
Secondly, if I try to convert this by splitting the field to show just the date in the first column and the time in the next, some of the date column cells show as “date” format and some as “General” with no obvious explanation as to why some display in different formats. They all still show as mm/dd/yyyy
Converting these as whole column does not work due to the different formats.
Is there something I have overlooked or some easier way to export Custom reports to correctly handle any date field?
I would argue that all date strings in exports should be in ISO international standard (YYYY-MM-DDT00:00:00Z), because this is what all spreadsheets and other systems should understand, and from there, be able to convert into any date object you like.
Although the custom report dates strings of MM/DD/YY… should also convert in a spreadsheet just fine.
The problem I assume you are seeing is if you are trying to get the spreadsheet to convert a date string into a date cell/object after you’ve already changed the string to your desired format DD/MM/YYYY, which it will incorrectly convert or not convert at all (as per below).
eg:
a date string of 01-04-2022 (MM-DD-YYYY) will convert to Jan 4th 2022
a date string of 01-04-2022 (DD-MM-YYYY) will still convert to Jan 4th 2022 (as the spreadsheet doesn’t know you intend the day to come first and just works on international standards)
a date string of 21-04-2022 (DD-MM-YYYY) will not convert at all as the spreadsheet sees the ‘month’ (21) as invalid.
(my experiences are based on Google Sheets, but I assume Excel and others would work the same)
Hi Adam…Great to hear form you and thanks for your comment. What you say is correct in a perfect world but for whatever reason, the export file I receive shows cells (after splitting the cell separating date and time into separate columns) for the date as a mix of integer values and date strings but not with any consistency. Excel will not automatically convert these date and time strings to a date value.
My only choice is to go through several steps involving extracting the date as a string, converting that from mm/dd/yyyy to dd/mm/yyyy format and then displaying as a date and not a string so I can do date related grouping / filtering etc.
Surely Treepl can provide a suitable export that handles date and time as does any other system. The fact that no one has raised this anywhere only made me feel it is something I have done incorrectly…
From my experience the spreadsheet sees the data as a Date/Time format. Doesn’t matter if it is Exel, Sheets or Numbers.
You can easily just reformat the date the way you want it by using the date formatting tools in the spreadsheet software.
If you want date and time in separate columns, I duplicate the column and then format the columns one into the date format and the other into the time format I want. Better then splitting the data because you can easily reformat the date or time.
@Rhatch - The problem is that the data exported for date fields are exported as a character string of the form “mm/dd/yyyy hh:mm:ss AM” and not as an integer that can be formatted as a date in whatever format is relevant to your country setting.
The only way you can process these dates is to extract the date as a text string and convert to a date value but here in Australia the DATEVALUE Excel function will not work by referencing the text field and instead we have to apply the following formula
=DATEVALUE(MID(E3,4,2)&“/”&+LEFT(E3,2)&“/”&+RIGHT(E3,4)) where E3 contains the extracted text string in the form “MM/DD/YYYY”
My point is that this is an awful lot of messing around all because the export function exports the DATE-TIME value as a text string and not the numeric integer as any other export function ever does.
I believe that the export function needs to comply with how al other export to Excel works…
@TopLeftDesigns This is why I don’t use Excel anymore. This works just fine in Google Sheets and Apple Numbers, but for some reason Excel doesn’t like the format.
There are two ways to do this in Excel use =TEXT(F2,"DD/MM/YYYY") will give you the date but it is still text so it will work if you want any formulas dealing with a date.
The other way is =DATEVALUE(F3) This will give you a date code. Then right click on the cell or column and select number format then date and set your date format you want.