Assign difference in a row's datetime to column

[Jakob_Petersen (Jakob Petersén)]

Hello,

I am trying to calculate the difference between data in two columns of the datetime type for a row, and then assign it to a third column. I have tried reading up here on the forum, but I can’t seem to get it to work. What am I doing wrong?

Thanks
J

[vishal.kp (vishal)]

Refer this to get clear view of subtraction of date time.

Date Subtraction

@Shin_Sam @sangasangasanga If the date format is an issue, a better way to do this would be to use DateTime.ParseExact() method and specify the exact format of the date: (DateTime.ParseExact(“10/01/2018”,“MM/dd/yyyy”,CultureInfo.InvariantCulture)-DateTime.ParseExact(“04/02/2018”,“MM/dd/yyyy”,CultureInfo.InvariantCulture)).Days This shall give you the same output (182) which as mentioned earlier you can divide by 30 to see if the dates have a 6-month difference or not.

[AshwinS2 (Ashwin S)]

Hi @Jakob_Petersen

give on the RHS as row.item(“EndTime”)-row.item(“StartTime”)

try to do Cdate function and check it

Thanks
Ashwin S

[Jakob_Petersen (Jakob Petersén)]

I don’t understand “give on the RHS” and “try to do Cdate function”…

[AshwinS2 (Ashwin S)]

Hi @Jakob_Petersen
before that do Cdate(row.item(“EndTime”).ToString(“hh:mm:ss”)-CDate( row.item(“StartTime”).ToString(“hh:mm:ss”)

try this

Thanks
Ashwin S

[Palaniyappan (Forum Leader)]

Hi
hope this expression would help you on this
row(“Hours”) = (Datetime.ParseExact(row(“EndTime”).ToString.SubString(0,10),“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture).Hour - Datetime.ParseExact(row(“StartTime”).ToString.SubString(0,10),“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture).Hours).ToString

Cheers @Jakob_Petersen

[Jakob_Petersen (Jakob Petersén)]

Thank you all for your input, but I made it work in the following way for anyone else that is interested:

With the first and second assign looking like this:

d1 = Datetime.ParseExact(row.Item(“StartTime”).ToString,“MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture)

d2 = Datetime.ParseExact(row.Item(“EndTime”).ToString,“MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture)

and the third assign looking like this:

timeDiff = (d2.Subtract(d1)).ToString(“hh:mm:ss”)