CODEWITHSUNDEEP
webi
user17086102
user17086102

Reputation: 5

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

Upvotes: 0

Views: 3414

Answers (1)

Isaac
Isaac

Reputation: 3363

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.

Upvotes: 1

Related Questions