Getting Date Time to reflect hh mm ss format like viewed in excel

Okay peeps, I've been banging my head against the wall and tried almost every "date/time/format/parse" deal on here and I'm not having any luck. In the attached excel document you see the field "Total Time" and how I want it to look in Alteyx (53:40:00)..but when you actually click on the cell it shows it as (01/02/1900 5:40:00 AM) which is what Alteryx is trying to use instead of the total time (i.e. cumulative total which will always be over 24 hours). When I try to parse it out into seconds or any other format its only using the 5 hrs 40 min...not the 53 hrs, 40 min? How do I get Alterxy to see this properly? Please help!

Data.xlsx

Parse

Predictive Analysis

Accepted answers

MichaelLaRose

Hi @Aquarock ,

I have updated the calculations to produce the full time split.

As it turns out you needed to baseline at 12/30/1899 not 1/1/1900.

Best,

Michael

Psudo-Date Format.yxmd

MichaelLaRose

@Aquarock

Happy to.

The short answer is that I used the wrong date for the Microsoft epoch.

The long answer is:

All dates in Excel (and many software solutions) are stored as seconds since their epoch. In this case Excel is using the Microsoft epoch. See https://en.wikipedia.org/wiki/Epoch_(computing)#Notable_epoch_dates_in_computing

In your case, you have entered your date in hh:mm:ss and told Excel that it is a date time format. As a result Excel stores the integer value for the number of seconds represented by the hours, minutes, and seconds you gave it. but doesn't have any other date information so it in effect thinks that date value you provided is in the first week of 1900.

When Alteryx is reading the fields, Microsoft Excel tells Alteryx that the file is a date time format and provides the full date time value to Alteryx.

To correct for the fact that we don't have dates associated with the data, we need Alteryx to back solve for the number of seconds that Microsoft actually had stored between the Microsoft epoch and the date time format it provided to Alteryx. Once we have that number of seconds it is fairly straight forward to get the time into hours, minutes, and seconds.

We then needed to create a string containing the values you had because Alteryx doesn't have a time format that allows for more than 24 hours to my knowledge.

All comments

MichaelLaRose

HI @Aquarock ,

You are actually running into two different issues.

Alteryx is reading the data as a date because Excel is seeing the data as a date and the easiest way to correct that is to just re-baseline the data to seconds from 1900-01-01 00:00:00.

See the attached workflow.

I have taken the number of seconds since that baseline and then identified the number of hours and minutes that represents.

Date parsing isn't going to work because Alteryx won't see hours over 24 as a valid date format and will result in a conversion error. so we are going to just convert directly from numeric fields to the formatted string you are looking for.

**Note, I found that Men's Solo was only 5 hours

Let me know if you have any questions.

Best,

Michael

Psudo-Date Format.yxmd

Aquarock

Hey Michael,

Yeah that is my whole issue in excel the represention display is correctly as [h]:mm:ss but the under laying information is what both excel and Alteryx want to use just as you saw in your example since the Men's Solo came out as only 5hrs and 40 min, when it should be 53hrs 40 mins....this has driven me crazy to say the least. I appreciate your feedback but it still not the solution I'm trying for....

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703