Calculate the difference between two date and time columns when the columns are seen as text

[Anonymous]

I have two columns, one labelled Merged, and the other labelled TIME IN.  The Merged column is seen as a date/time field.  The TIME In field is seen as a text field.  When I try to calculate the time in between, I get the following error:  "Cannot convert value " of type Text to type Number."  How do I change these columns types to date/time so I can subtract?

[v-shex-msft]

Hi @Anonymous ,

If you want to calculate date time value with other type value, I'd like to suggest you use date functions to convert them to datetime first.(e.g. datevalue, date...)

Can you please share some sample data and expected result for test?

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, thank you for replying.  I was able to go back to my original export and select a different date/time field.  Now both fields are concatenated and are configured as date/time fields.  Not all is well though.  I'm still getting an error when using a DATEDIFF or other ways to get the difference between the two date/time fields.  Here is some sample data.  I want to get the difference between the date and time.  The Episode_Activation_Time is the starting time, the TIME_In is later.

---

Episode_Activation_Time	TIME_In
7/1/2019 0:00	7/1/2019 1:40
6/28/2019 17:00	7/1/2019 9:11
7/1/2019 10:50	7/1/2019 11:55
6/20/2019 8:45	7/1/2019 11:01
6/27/2019 8:00	7/1/2019 13:10
7/1/2019 14:00	7/1/2019 14:05
6/26/2019 13:30	7/1/2019 11:01
6/12/2019 16:30	7/1/2019 14:10
7/1/2019 17:02	7/1/2019 17:20
7/2/2019 1:00	7/2/2019 1:00
7/2/2019 4:15	7/2/2019 4:15
7/2/2019 15:20	7/2/2019 15:30

[v-shex-msft · Accepted solution]

HI @Anonymous ,

You can try to use following calculated column formula to calculate diff between two datetime ranges:

Diff =
VAR _min =
    IF (
        [Episode_Activation_Time] <> BLANK (),
        [Episode_Activation_Time],
        MINX ( ALL ( Table[Episode_Activation_Time] ), [Episode_Activation_Time] )
    )
VAR _max =
    IF (
        [TIME_In] <> BLANK (),
        [TIME_In],
        MAXX ( ALL ( Table[TIME_In] ), [TIME_In] )
    )
RETURN
    DATEDIFF ( _min, _max, SECOND )

Regards,

Xiaoxin Sheng