Start a new topic

Code editor

In the code editor I'm trying to convert 2 timestamps to a number.  This is how it's done in excel.  Does anyone know how to import a timestamp value and then covert it?  Thanks.

image


 


Hi Mary,

Based on what I'm seeing in your screen shot this should do the trick

Dim Date1 As Date = Convert.ToDateTime(Import.Fields.GetByExcelName("A").Value)
Dim Date2 As Date = Convert.ToDateTime(Import.Fields.GetByExcelName("B").Value)
Dim Hour1 As Integer = Hour(Date1)
Dim Hour2 As Integer = Hour(Date2)
Dim Timedifference As Integer = Hour2 - Hour1

Import.Fields.GetByExcelName("C").Value = Timedifference

Thanks,

John
John,

Thank you for your help. I also need to show the time with quarter hours like 2.25, 2.5 and 2.75. Can you help me with that? The excel format for the column (which didn't show) was h:mm.

Mary

My knowledge of vb is right around kindergarten level, so I'm sure there is a MUCH simpler solution, but this worked in testing for me.

 Dim Date1 As Date = Convert.ToDateTime(Import.Fields.GetByExcelName("A").Value)

        Dim Date2 As Date = Convert.ToDateTime(Import.Fields.GetByExcelName("B").Value)

        Dim Hour1 As integer = Hour(Date1)

        Dim Hour2 As integer = Hour(Date2)

        Dim minute1 As double = minute(Date1)

        Dim minute2 As double = minute(Date2)

        

        Select Case minute1

            Case 0 To 7

                minute1 = 0

            Case 8 To 22

                minute1 = .25

            Case 23 To 37

                minute1 = .5

            Case 38 To 52

                minute1 = .75

            Case 53 To 59

            minute1 = 1

         End Select

         

         Select Case minute2

            Case 0 To 7

                minute2 = 0

            Case 8 To 22

                minute2 = .25

            Case 23 To 37

                minute2 = .5

            Case 38 To 52

                minute2 = .75

            Case 53 To 59

            minute2 = 1

         End Select

        

        Dim Timedifference As double = (Hour2 + minute2) - (Hour1 + minute1)

       

        Import.Fields.GetByExcelName("C").Value = Timedifference

Check out https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

You should be able to skip the Select/Case and just do something like Date1.ToString("h:mm")
I'm using the Select Case because it works! When I have time I'll see if I can use something else but for now this is great. Thank you both.
Login or Signup to post a comment