6 Replies Latest reply on Jul 22, 2015 1:49 PM by Kishan Balar

# Used DATEPARSE, having issues with AM/PM

So I had to use a DATEPARSE formula to convert a date that was only being recognized as a string into date/time. When doing so, those times that started with 00 should have become 12AM (i.e. 00:12:34 should be 12:12:34AM) and those that start with 12 should be 12 PM (i.e. 12:35:15 should be 12:35:15 PM).

The issue that is occurring is that times that start with 12 are not being made 12PM, they are just staying 12AM. Does anyone know how to fix this issue keeping in mind that I had to use the DATEPARSE function.

Any help is appreciated. Thanks.

P.S. the DATEPARSE formula I used is DATEPARSE('yyyy-MM-dd-hh.mm.ss.',[Call Start Timestamp])

I have been reading online about the DATETIME formula but am unsure if that can work here and how it would work with the DATEPARSE calculated field I had to create.

Any help is appreciated. Thanks.

• ###### 1. Re: Used DATEPARSE, having issues with AM/PM

Hello Kishan,

Maybe something like this could work?

IF DATEPART( 'hour', [YOUR PARSE DATE]) = 0

ELSEIF DATEPART( 'hour', [YOUR PARSE DATE]) = 12

END

If the Hour is equal to 0, then it adds 12 hours, if it is equal to 12 it subtracts 12 hours, else it returns the field.

You may need to play around with it, but I think that should give you the results you need.

Regards,

Rody

• ###### 2. Re: Used DATEPARSE, having issues with AM/PM

Hi Kishan,

Can you provide an example of what your [Call Start Timestamp] data looks like? I'm curious to know why you would need to use the DATEPARSE function. Assuming the [Call Start Timestamp] data looks like what I have included in the attached workbook, I created a calculated field named [DateTime] and simply wrapped the DATETIME function around [Call Start Timestamp]. Truly hope this helps.

Kind Regards,

-Manny

• ###### 3. Re: Used DATEPARSE, having issues with AM/PM

I have attached a sample of a few call time starts/ends. The first column that is up shows the string, the second column shows what the DATEPARSE produced, and the third column shows what a DATEPART formula produced.

• ###### 4. Re: Used DATEPARSE, having issues with AM/PM

Hey Kishan,

I figured out how to handle this on the other thread.

Re: DateDiff giving negative value when it shouldn't

I am still trying to figure out why Tableau is randomly making both 12 and 00 into 12:00 AM? Maybe a bug in Dateparse?

Rody

• ###### 5. Re: Used DATEPARSE, having issues with AM/PM

Thank you Kishan,

The HOUR symbol in your DATEPARSE date format: ('yyyy-MM-dd-hh.mm.ss.',[Call Start Time]) is returning HOUR in AM/PM (1-12).

I created a calculated field called [Real Call Start Timestamp.Revised] that updates the HOUR symbol to H (or hour in day (0-23)): DATEPARSE('yyyy-MM-dd-H.mm.ss',[Call Start Time]). This field returns Call Start Times with hours equal to 00 as AM and hours equal to 12 as PM

I've added the revised field to your view so that you may have a side-by-side comparison of the results.

In addition, I've provided a link below which is a great resource in formatting dates and times.

Kind Regards,

-Manny

Formatting Dates and Times - ICU User Guide

• ###### 6. Re: Used DATEPARSE, having issues with AM/PM

Wow. I can't believe it was as simple as that. Thank you for your help. Thanks Rody Zakovich for your support throughout the day as well.