Search code examples
webi

Convert Date from Text to Date Time for WebI 4.2

I am trying to calculate the duration between StartTime and CurrentDate. However, the StartTime data comes in Text Form as below. I tried converting it using =ToDate([StartDate];"MMddyyyy") but got #ERROR instead.

StartTime data

Solution

  • It would be best if your StartTime was stored and retrieved as DateTime data type.

    Assuming that is beyond your control, you need to convert your StartTime string to a date. However, I cannot figure out how to deal with the milliseconds so I got rid of them. To do so I found the position of the decimal. Here is my formula for a variable called Decimal Position.

    =Pos([Start Date Text]; ".")

    I then take the everthing to the left of that and store it in Start Date Text Trimmed.

    =Left([Start Date Text]; [Decimal Position] - 1)

    Now convert that to a Date data type in Start Date DateTime Type.

    =ToDate([Start Date Text Trimmed]; "yyyy-MM-dd HH:mm:ss")

    Finally you can find the amount of time elapsed.

    =TimeBetween([Start Date DateTime Type]; [Current Date Time]; HourPeriod)

    Adjust the last parameter to be whatever period you need.

    For some reason I seem to be getting an extra 4 hours.

    enter image description here

    That may be an issue with my settings that you do not encounter. It might be a bug. If it is you could adjust your Start Time using the RelativeDate() function.