BusinessObjects Board

Convert text to time, and do calculation

building reports webintelligence
#1

hello all,

I’m using XIR2 webi, and I don’t have the option to make any changes in the universe.

I have a field which is text and contains a time in 24hour format “13:15:21”.

How do I change the time field to an actual time (or date) since it’s in 24 hour format? If it were 12, I guess I could use ToDate([TimeField];“hh:mm:ss”)

Thanks in advance,
hagnik

hagnik :us: (BOB member since 2006-08-01)

#2

Hi,

For 24h format of the time use:

ToDate([TimeField];"HH:mm:ss")

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Excellent, I should have known better.

How about the second part of my question? I’m aware of the =RelativeDate() function, but don’t know how to use for hours. I suppose I could use a fraction (decimal) of a day, but that would not keep the hours/mins/secs accurate.

any help on this would be great.

psuedocode:
NewTime = OldTime - 5Hours

Thanks,
hagnik

hagnik :us: (BOB member since 2006-08-01)

#4

Hi,

I do not see the second part of the question in your first post :?

Unfortunately, the RelativeDate() function works only with the days, not with the fractions of day.

What you could do is:

  • Convert the [old time] date to a number of hours from some point in the past (let’s say from Jan 1 of this year or even earlier if it’s needed)
  • Then subtract 5 hours from this number
  • And convert the result back to date, that will be [NewTime]

Something similar (getting the difference between 2 dates) was discussed here:

It may help you to get an idea.

Marek Chladny :slovakia: (BOB member since 2003-11-27)