11 Replies Latest reply on Jan 5, 2015 8:12 AM by daniel.rodrguez

DATEPARSE doesn't parse correctly some hours in AM or PM

daniel.rodrguez Dec 23, 2014 5:52 AM

Hello,

I'm parsing an timestamp from an Excel file, but in some cases, the DATEPARSE function is not parsing correctly the hour of the timestamp, and where it should consider PM it's considering AM, and it seems that it's only happening when the original hour is between "12 PM and 13 PM", it converts the hour between "00 AM to 01 AM".

See printscreens below below:

"Horodotage" column (original string with date/hour)

"Date Hour" column (parsed data)

Do you have any idea of what can be wrong?

Best regards.

Daniel

1. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

KK Molugu Dec 23, 2014 6:58 AM (in response to daniel.rodrguez)

Daniel.

Can you try with this syntax,
Dateparse ("dd/MM/yy hh:mm:ss:SSS", [Horodotage])

..kk
Like Show 0 Likes(0)

Actions
2. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

daniel.rodrguez Dec 23, 2014 7:20 AM (in response to KK Molugu)

Hello Karanuker,

Thanks for your reply. Nevertheless, it did not fix it.
I've two cases:

Using just Uppercase in the ".SSS" doesn't change anything.
When changing to Uppercase in the "MM", my data in the worksheet dissapears.
Like Show 0 Likes(0)

Actions
3. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

KK Molugu Dec 23, 2014 7:53 AM (in response to daniel.rodrguez)

Can you attach ur xls worksheet.
..kk
Like Show 0 Likes(0)

Actions
4. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

daniel.rodrguez Dec 23, 2014 9:16 AM (in response to KK Molugu)

For sure. Here you have it.
https://onedrive.live.com/redir?resid=24C7F646DE8C67E7!1178&authkey=!ALeYZEscE6hKTdc&ithint=file%2cxlsx

Best Regards.
Like Show 0 Likes(0)

Actions
5. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

KK Molugu Dec 23, 2014 9:34 AM (in response to daniel.rodrguez)
Daniel:

The function worked fine for me and check the attached workbook and let me know if I am missing something;

In the below image; Date Hour is with the dateparse that I sent you and the Data Hour OLD is with what you had. Hope this helps

..kk

dateparse.twbx 49.3 KB
Like Show 0 Likes(0)

Actions
6. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

daniel.rodrguez Dec 23, 2014 10:24 AM (in response to KK Molugu)

Hello Karunaker,

Thanks again for your prompt feedback.
It seems that could be something with my PC... I've noticed that not only the hour of 12 PM to 00 AM is not parsed correctly, but there are also some minutes that are not OK.

Unfortunately, I'm a Tableau Public user and I couldn't open the workbook you shared.

Could you paste the printscreens?

Best regards.

Daniel.
Like Show 0 Likes(0)

Actions
7. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

KK Molugu Dec 23, 2014 10:41 AM (in response to daniel.rodrguez)

Daniel.
Dateparse function depends on the locale setting on the computer and chexk this link.
Understanding the DATEPARSE Function | Tableau Software

Not in fron of the computer and will upload to public today. Can you tell me the locale settings on your laptop so i can check that

..kk
Like Show 0 Likes(0)

Actions
8. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

daniel.rodrguez Dec 28, 2014 9:32 AM (in response to KK Molugu)

Hello Karunaker,

Sorry for this late reply, but due to holidays season I couldn't have this info before.
Here you have the locale settings in my PC.

Have you had the chance to upload the workbook in Tableau Public?

Thanks
Daniel
Like Show 0 Likes(0)

Actions
9. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

KK Molugu Dec 28, 2014 9:49 AM (in response to daniel.rodrguez)

Daniel:

I just uploaded the workbook to the Public server and here is the link:
https://public.tableausoftware.com/views/dateparse/Final?:embed=y&:display_count=no

I do not see the local PC settings on your machine.

..kk
Like Show 0 Likes(0)

Actions
10. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

KK Molugu Dec 28, 2014 9:58 AM (in response to daniel.rodrguez)

Daniel:

Instead of Dateparse function, just try datetime and I am hoping that this should work fine too. I still see some issues with AM/PM with the dateparse also.

datetime([Horodotage])

..kk
Like Show 1 Likes(1)

Actions
11. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

daniel.rodrguez Jan 5, 2015 8:12 AM (in response to KK Molugu)

Thank you Karunaker... DATETIME worked very well.

Best regards.
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

DATEPARSE doesn't parse correctly some hours in AM or PM

1. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

2. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

3. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

4. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

5. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

6. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

7. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

8. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

9. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

10. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

11. Re: DATEPARSE doesn't parse correctly some hours in AM or PM

Sign In or
Create your Account

Actions

More Like This

Sign In or Create your Account

Sign In or
Create your Account