hh : mm : ss format converted to hour (numeric) value

[#1]

Hi,

I was thinking if this is possible , this time stamp "4:44:30" would give something like this "4.44" in numeric value

Thanks!

[sushil353]

Use Date#() function...

[tresesco]

Try this:

Time(TimeStamp#('4:44:30','hh:mm:ss'),'hh:mm')

[Gysbert_Wassenaar]

Try this in a textbox: =time(floor(Time#('4:44:30','hh:mm:ss'),1/1440),'hh:mm:ss'). If that does what you want you can change the last hh:mm:ss in hh:mm to set the time display format to hours and minutes again.

[swuehl]

If you want  a numeric value of 4.44 from your time value, try (with a standard time format set to 'hh:mm:ss' in the script):

=hour('4:44:30')+minute('4:44:30')/100

This doesn't make much sense to me, though. Maybe you want the numeric value as fractions of an hour, i.e.

=interval#('4:44:30','hh:mm:ss')*24

which returns 4.74, i.e. 4 hours and almost three quarters of an hour (seems ok, close to 4:45:00).

If you want just a different text format of your time stamp, use one of the expressions suggested above or

=interval(Interval#('4:44:30','hh:mm:ss'),'h.mm')

for a decimal point as hour/minute separator.

[#6 · Accepted solution]

Thanks for the output guys!,

just solve this on my personal research on math equations

Convert time stamp into hour decimal value :

hour( interval(([time_stamp]/86400), 'hh:mm:ss') )

           + (

          ((Minute (interval(([time_stamp]/86400), 'hh:mm:ss')) * 100) / 60)

  +      (Second (interval(([time_stamp]/86400), 'hh:mm:ss')) * 100) / 3600

     ) / 100

[sanketkhunte]

Can we use multiple aggregation function in time formatting.. I am trying below -:

=interval(timestamp#(((100719)/6), 's'), 'hh:mm:ss')

it is not working  - please help me on this.

thanks

Sanket

[tresesco]

Sanket,

This doesn't work because 100719/6 produces a number with decimal and that can't be parsed using only 's'. Try to reduce the number to an integer by using floor()/ceil() like:

=interval(timestamp#(floor(100719/6), 's'), 'hh:mm:ss')

[swuehl]

What do you mean with multiple aggregation function? I don't see a single aggregation function in your expression.

Maybe try

=interval((100719/6)/(24*60*60),'hh:mm:ss')

24*60*60 is the number of seconds in a day (so you convert your seconds to fractions of days, that's what interval() expects.