Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a issue.
=Timestamp('11:12:55','HH:MM') // it will return 11:12
But in my case, It is not give correct result.
I have date column "EndTime" and I wrote the expression like =Timestamp(EndTime,'HH:MM') in text object. but it is not give a correct result.
Wanted result is= 2:06
Pls Help in this..
Thanks In advance..
Regards,
Kabilan K.
Kabilan,
I am not sure I understand your issue. Using your exact data:
LOAD StartTime, EndTime
,interval(round(EndTime-StartTime,interval#('01','mm')),'mm') as DiffMinutesRounded
,interval(round(EndTime-StartTime,interval#('01','mm')),'ss') as DiffSecondsRounded
,interval(EndTime-StartTime,'ss') as DiffSeconds
INLINE [
StartTime, EndTime
23:03:45,23:04:59
23:03:06,23:04:59
23:04:08,23:04:06
];
I do get these results after reloading:
StartTime | EndTime | DiffSeconds | DiffSecondsRounded | DiffMinutesRounded |
23:03:06 | 23:04:59 | 113 | 120 | 02 |
23:03:45 | 23:04:59 | 74 | 60 | 01 |
23:04:08 | 23:04:06 | -2 | 0 | 00 |
Seems all reasonable to me if you want to round the intervals to the nearest minute.
But it is not fine with this data: EndTime:23:04:59
StartTime:23:03:06
about this data it returns 0
But I want this also 1.
I do get 02, not 0, please check above. And why do you want to display this interval as 1 minute (113 seconds are closer to 2 minutes).
Regards,
Stefan
Hi,
Sorry about my bad,
my question is wrong.
Here I corrected them.
I worte a script like this.
Interval(round(EndTime-StartTime,interval#('01','mm')),'mm') // Your Script
it is fine with this data: EndTime:23:04:59
StartTime:23:03:45
about this data it returns 1
But it is not fine with this data: EndTime:23:04:10
StartTime:23:03:59
now it ll return 11 sconds ryt? so rounded minutes is 0 (round(29seconds,1) it will return 0 and round(31seconds,1) it will return 1)
But I want this also 1.
so that I have changed the script like below.
Interval(Ceil(EndTime-StartTime,interval#('01','mm')),'mm')// it will returns 1 bez difference is 2 seconds.
(Ceil(29seconds,1) it will return 1 and round(31seconds,1) it will return 1)
but it returns also 1 for this data : EndTime:23:04:08
StartTime:23:04:06
but here I want the value as 0
Please help me.
Thanks in advance.
Regards,
Kabilan K.
now it ll return 11 sconds ryt? so rounded minutes is 0 (round(29seconds,1) it will return 0 and round(31seconds,1) it will return 1)
But I want this also 1.
...but it returns also 1 for this data : EndTime:23:04:08
StartTime:23:04:06
but here I want the value as 0
It's still unclear to me when you want to return 0 or 1. round() will return the neareast whole minute.
If you don't want this, you need to specify what you want else.
Like for example:
"I want to round to the nearest minute, except for a difference of exactely 11 seconds, where I want always 1 returned."
But I don't think that's what you want. Please specify.