I have a date in a cell, in the attached. 11/31/2023 when I try to converter it to MM/dd/yyyy hh:mm:ss AP it will not read the first date, even when I put M/d/yyyy or MM/dd/yyyy. It would be pretty cool if the system just read the current date format so you didnt have to play matching. If you could let me know why this wont work, I would appreciate it.
EE Health New.csv (58 Bytes)
31 Nov 2023 doesn’t exist, so I’m not sure what Easy Data Transform is supposed to do? Perhaps it could output a better warning.
Do you mean to fill in the Format from field automatically? Or something else?
That is a good idea, by providing records’ row number in the right pane warning tab with message describing the issue with the Date, like it is not a valid date or not in proper date format, or whatever reason causing the issue. Just like in this case November does not have 31 days.
Unfortunately the date conversion library we use does not distinguish between:
However I will try to make the warning message a bit clearer about this.
ha. yes, you are correct that its not a date. dumb move on my part. I picked up the sample file from someone else and just put my head down without thinking through it. however, there has been other times where the transformer wont pick up the current date format. IF that happens again, I’ll post here.
I think the rest of the string already answered the question, but yes, it would be nice if it saw 2023-11-30 if it would recognize it as yyyy-MM-dd and then all you needed to do was tell it where to go. That would also indicate that it’s actually recognizing it as a date in the first place before you try to go through the game of figuring out exactly what the current format is. I often get files where the actual format and physically what I can see can be different. Dates in excel are the bane of my existence.
It already tries to do that, working through the list of formats in Preferences>Dates looking for a format that matches. But, if you have invalid dates in a column such as "31-11-2023” or “x”, then it can’t work out the format for the column.
It now shows a more helpful message in the warnings tab when you try to convert non-existent dates. Unfortunately there doesn’t seem to be a way to tell the difference bewteen invalid and badly formatted dates in the library we call.