Time Diff Function

[Nargess]

Hi,

I am new to Alteryx trying to subtract 2 time columns and convert that to hours, minutes and seconds.

I have the syntax and it works perfect for some rows, but the hours is not working for some special cells, Can you please check my hours and find the problem

I have converted both Final and First time to time format before.

Diff Seconds: DateTimeDiff([Final Time],[First Time] ,'Second')

Then for seconds:  Mod([Diff Seconds], 60)

For Minutes: Mod([Diff Seconds]/60, 60)

For Hours: ([Diff Seconds]/3600)

And finally : ToString([Hours])+":"+PadLeft([Mins],2,"0")+":"+PadLeft([Seconds],2,"0")

Can you please tell what is wrong with my syntax?

[atcodedog05]

Hi @Nargess ,

Can you provide some sample data on a excel/csv file.

That can help us to provide you a solution.

[jdunkerley79]

Based on what you posted, I think all you need do is cast the Seconds and Minutes:

ToString([Hours])+":"+PadLeft(ToString([Mins]),2,"0")+":"+PadLeft(ToString([Seconds]),2,"0")

Quick sample attached 

[PhilipMannering · Accepted solution]

Hi Nargess,

Think it's all broadly correct.

You just need to make sure your hours minutes and seconds are strings before padding them with zeros.

You might also need to round the hours down to get the correct answer.

Also, Final Time and First Time must be datetime datatypes.

See attached to see a working version...

[Nargess]

Thank you all,

I have tried all possible options I found here. Attached please find my Excel file.

[Nargess]

Thank you Philip,

I will check and Let you know.

[jdunkerley79 · Accepted solution]

Have attached a sample-based off Excel sheet.

The added complication is parsing the input times:

MAX(0, DateTimeDiff(
   DateTimeParse([Final Time], "%H:%M:%S"),
   DateTimeParse([First Time], "%H:%M"),
   "seconds"))