Diff in Seconds, Hours and Mins with retroactive dates, how to do?

Hello all!!!

I have created a workflow that does a mathematical calculation between Datetimenow and another column with Seconds, Hours and Mins from retroactive dates or today's date as well.

The problem is in the below example:

Today: 26/07/22 15:58:06

Column with: 25/07/22 11:58:06

Result: null.

Is there any way to substrate this formula accounting for previous dates as well? (it can be more than a day or two)

Thanks in advance!!

Workflow sample.yxmd

Workflow

Accepted answers

DataNath

How does this look @Joker_Hazard?

Just needed to wrap the [Diff in Seconds] expression in the abs() function if suitable:

Workflow sample - amended.yxmd

All comments

DataNath

How does this look @Joker_Hazard?

Just needed to wrap the [Diff in Seconds] expression in the abs() function if suitable:

Workflow sample - amended.yxmd

DataNath

Just a follow up response - the reason this was nulled originally is that your [Diff in Seconds] field is currently set to a 'Byte' data type which has to be a positive integer (https://help.alteryx.com/20221/designer/data-types). In the instance you highlight, the datediff is negative and therefore the record will null. However, even if you were to fix that, because the answer is now negative, you need to use the CEIL() function instead of FLOOR() to get the correct breakdown and subtraction of hours from minutes, minutes from hours etc - I've split off the negative results and demonstrated what I'm talking about below:

So if you wanted to keep these negatives then you could either split the stream, treat them differently like this and then rejoin, or use an if statement to say that if positive then use floor() else use ceil().

Workflow sample - showing negatives.yxmd

Joker_Hazard

@DataNath Thanks DataNath!!!

May I ask one question though?

As you can see, there is a three day difference on the first line and "tempo total em OFAC" should be accounting for this time interval.

If I may ask, how can I add this? I now there is this limitation of hour:min:seconds but would add another variable like day:hour:min:seconds.

Can you help me out with this or should I create another post? I tried creating that but still no sucess.

DataNath

No problem at all @Joker_Hazard! To add the day element, surely you could just add another expression to your formula tool, using the DateTimeDiff() function before your hours and then follow the same method as you have been for the others, but this time factoring the subtraction of days into your hours/minutes/seconds calculation where applicable so they still pull through as expected.

Joker_Hazard

It worked

Thanks again

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703