Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to convert the difference between two time stamps (in seconds) to the Num() format so that I can perform calculations using the result.
I am using the interval function to calculate the number of seconds between two time stamps:
Interval('9:08:15 AM' - '9:07:42 AM', 'ss')
The result for the above example is 33 seconds, which is correct.
I want to be able to add a number from another field to those seconds. For example, add 5 to the result above. If I try to perform this addition, the interval value reverts to a time decimal. For example:
5 + Interval('9:08:15 AM' - '9:07:42 AM', 'ss') = 15.000381944444
The result I am looking for is 38 seconds. Using any combination of Num() and Text() has not helped.
Any ideas?
Right, QV time and date values are numbers and stored as fractions of days.
Interval('9:08:15 AM' - '9:07:42 AM', 'ss') is just formatting the underlying number ( a small fraction) as text showing 33.
So you need to multiply the underlying number with 24*60*60 to get a number 'in seconds', or you could try
Num#(Interval('9:08:15 AM' - '9:07:42 AM', 'ss')
Try Interval('9:08:15 AM' - '9:07:42 AM' + maketime(0,0,5), 'ss')
I should have added that I need to multiple the resulting seconds by a dollar amount. This helps to get the total number of seconds, but I still need the result to be in a number format to perform additional calculations.
It looks like the this might actually get the correct answer:
Num(Interval('9:08:15 AM' - '9:07:42 AM', 'ss') *24*60*60)
Right, QV time and date values are numbers and stored as fractions of days.
Interval('9:08:15 AM' - '9:07:42 AM', 'ss') is just formatting the underlying number ( a small fraction) as text showing 33.
So you need to multiply the underlying number with 24*60*60 to get a number 'in seconds', or you could try
Num#(Interval('9:08:15 AM' - '9:07:42 AM', 'ss')
I think
=interval(Timestamp#('09:08:15 AM', 'hh:mm:ss tt') - Timestamp#('9:07:42 AM', 'hh:mm:ss tt') + interval#(5, 'ss'), 'ss')
Uggghhhh so many times in the past I struggled and couldn't understand why NUM didn't give me what I wanted. Num# is the key. Glad I found this.