How to substract two dates (with h,m,s)

[Anonymous]

Hi all,

I need your help!

I have this situation:

I would like to substract both dates (DATE & TIME and Start Time).

In my mind appear this step: creates a new personalized column doing DATE&TIME - Start time. The result is something like Finish time column.

When I close and apply and take all the data to the desktop, the program isn ot reading well its type.

What should I do?

Thank you so much.

PS: this Finish Time colum will be used to substract with other date.

Note that all the result dates will last less than 24h, so the first number (day) will be 0 (0.07:25:52)

[Icey · Accepted solution]

Hi @Anonymous ,

"Duration" type can't be passed to Data view. Since all the result dates will last less than 24h, it is suggested to create a calculated column like below:

Column =
VAR DateDiffSeconds =
    DATEDIFF ( [Start Time], [DATE & TIME], SECOND )
VAR Hours =
    TRUNC ( DateDiffSeconds / 3600 )
VAR Minutes =
    TRUNC ( ( DateDiffSeconds - Hours * 3600 ) / 60 )
VAR Seconds = DateDiffSeconds - Minutes * 60 - Hours * 3600
VAR Result =
    Hours & ":"
        & FORMAT ( Minutes, "00" ) & ":"
        & FORMAT ( Seconds, "00" )
RETURN
    CONVERT ( Result, DATETIME )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Subtracting two DateTime columns will give you a duration.  You likely need to wrap your calculation in one of the Duration functions to get a usable number.  For example, Duration.TotalHours([Date & Time] - [Start Time]) would get you the # of hours between them in decimal form.

Regards,

Pat

[camargos88]

@Anonymous ,

If you substract it, you will have the duration data type. Is that what you want ?

Also, based on your image, it has the "any" data type, you need to convert it in order to do calculations.