Dates are tricky!

…ooops! Didn’t work. Maybe the dot in the “a.m.”/”p.m.” is screwing the parsing? We will remove the dot with a calculated field and after that we will change our calculation to a date field (remember that [Original Date] is a text field and after using REPLACE will stay as text):

New Date
REPLACE([Original Date],".","")

and…

Great, it looks like we are ready to go! Let’s do a bar chart to see the distribution of our data:

Hum! This isn’t right, according to my client we had data from January 2018 to February 2019. What’s happening?

After careful inspection of the underlying data I found this:

It look like for some reason the date format changes from day/month/year to month/day/year but our date parsing formula is set like this:

DATEPARSE ( "dd/MM/yyyy hh:mm:ss a", REPLACE([Original Date],".","") )

So, our [New Date] field is considering the first row in the picture above as December 4, 2018 instead of April 12, 2018! And the second row is fine.

To solve this, I decided to separate the date from the time on the [Original Date] field, make a formula that uses a different parsing according to the date format they are on the raw data and then paste the time section again. I came up with this:

Date NEW
DATE(IF ISNULL(DATEPARSE("MM/dd/YYY",SPLIT([Original Date]," ",1))) OR 
DATEPARSE("MM/dd/YYY",SPLIT([Original Date]," ",1))>= #2019-03-01#
THEN DATEPARSE("dd/MM/YYY",SPLIT([Original Date]," ",1))
ELSE DATEPARSE("MM/dd/YYY",SPLIT([Original Date]," ",1))
END)

Let’s break it out from inside out:

1. The SPLIT formula is just taking the date section from the [Original Date] field.
2. The DATEPARSE is just parsing the date on our [Original Date] field.
3. ISNULL is just catching the rows where the parsing throws an error, since it is set as “MM/dd/YYY” it will return NULL on those dates that start with a number bigger than 12.
4. With the second DATEPARSE we are checking of the dates are later or equal to March 1^st 2019, why? Well because our raw data doesn’t have records beyond February 2019. This project was an static dataset in other cases we would have to use a formula to determine the max date.

If points 3 and 4 are TRUE that means that the raw date is on “dd/MM/YYY” format is that’s not the case then they are in “MM/dd/YYY”.

OK, that should solve the problem, let’s see:

Yes, we’re back in business! Now we need to add the time.

For the time section I used the following formula:

Time NEW
DATEPARSE("HH:mm:ss aa",
    IF LEN([Original Date])=24
    THEN REPLACE(RIGHT([Original Date],13),".","")
    ELSE REPLACE(RIGHT([Original Date],12),".","")
    END)

Let me describe it, if the length of the [Original Date] field is equal to 24 (24 is the number of characters on the [Original Date] field when the hours are in double digit for example 11:23:42) then take the last 13 characters, for example “11:23:42 a.m.” and then remove the dots so we end up with “11:23:42 am” if it’s different to 24 then just take the last 12 characters and do the same procedure. Finally parse the time with the following format “”HH:mm:ss aa”.

And as a last step to have our full date we just paste our date and time using the MAKEDATETIME formula and we are DONE!

At first it looked as an easy task and ended up as a long process but hey, that’s what we are here for, don’t you think? What I learnt from this is that the exploratory part of the data is of great importance for our analysis and our visualization, “garbage in, garbage out” they say.

Hopefully this first technical post will be useful for you, I know is a unique case but maybe you’ll find something similar on the field and this post will pop in your head.

Please leave your comments about the post, the writing or anything else, constructive criticism is welcome.

Nos vemos!

Adolfo