Converting hr min sec into numbers

S

ShiferawA

I am looking at invoices that have captured time spent on activity,
the system reports duration in hr min sec ( 5:47:06). I need to
convert this into a numbers that I can manipulate I could create a
formula such as = (5*3600) + (47*60) +06, however that is very manual
and I am looking at too many numbers. Is there something else I can
do? Thanks Shiftaw
 
D

David Biddulph

=A1*24*60*60 (or =A1*86400) and format the result as General or Number, not
as Time.
 
B

bj

the way time is stored it is a fraction of a day
If you change your format to general
it should dispay 0.241042
if not it is text and you will need to convert it to time
one method to convert it to time is
=time(value(left(A1,if(len(a1)=7,1,2))),value(mid(A1,if
len(A1)=7,3,4),2)),value(right(A1,2)))

will the fraction of a day do what you want?
if you need it in seconds

=A1*24*3600
 
A

aidan.heritage

=A1*24*60*60 (or =A1*86400) and format the result as General or Number, not
as Time.
--
David Biddulph






- Show quoted text -

Might be worth mentioning the custom format of [hh]:mm etc - this
would POTENTIALLY remove the need to change the time values
 
D

David Biddulph

Yes, just to use =A1 and format as would give a number that would
display as 208626, but if (as the OP says), the purpose is to have a number
to use in further manipulation, this didn't seem the ideal route.
--
David Biddulph

Might be worth mentioning the custom format of [hh]:mm etc - this
would POTENTIALLY remove the need to change the time values
 
S

ShiferawA

Yes, just to use =A1 and format as would give a number that would
display as 208626, but if (as the OP says), the purpose is to have a number
to use in further manipulation, this didn't seem the ideal route.
--
David Biddulph






Might be worth mentioning the custom format of [hh]:mm etc - this
would POTENTIALLY remove the need to change the time values
=A1*24*60*60 (or =A1*86400) and format the result as General or Number,
not
as Time.
--
David Biddulph

I am looking at invoices that have captured time spent on activity,
the system reports duration in hr min sec ( 5:47:06). I need to
convert this into a numbers that I can manipulate I could create a
formula such as = (5*3600) + (47*60) +06, however that is very manual
and I am looking at too many numbers. Is there something else I can
do? Thanks Shiftaw- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -


Thank you this was very helpful....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top