Round/Display (HR:MN:SEC) data in decimal HR format for Time C
Is there a tutorial somewhere that steps you through the conversions of HR
time formats hr:mm:ss to data that can be easily analyzed? I want to create
histograms, averages, standard deviations, etc.
"T. Valko" wrote:
You're welcome. Thanks for the feedback!
Biff
"Nauj Solrac" wrote in message
...
This is great.
Thank you very much
"T. Valko" wrote:
If these times are manually entered there's no getting around the limit
of
9999:99:99 (search Excel help for limits and specifications). It's going
to
be evaluated as a TEXT string. So, if you have in A1: 32570:57, =A1*24
returns #VALUE! because your trying to multiply a number and a TEXT
string.
A way to get around this is to format cell A1 as TEXT and then just
remove
the *24 expression for my formula:
A1 = 32570:57 (a TEXT string)
=LEFT(A1,FIND(":",A1)-1)+RIGHT(A1,2)/60
Result: 32570.95 (numeric)
Biff
"Nauj Solrac" wrote in message
...
Thanks Biff, but the result should be 32570.95 in B1
The original formula works fine but if I enter manually in A1 any value
grater than 9999:59:59 the result is an error. By the way A1 is
formatted
as
Time [hh]:mm:ss and B1 as number with decimals places.
Regards.
"T. Valko" wrote:
The maximum *manual* time entry is 9999:99:99.
You can enter 32570:57 in the cell but it will be a *TEXT* string. You
can
still use that value in your calculation:
=LEFT(A1,FIND(":",A1)-1)*24+RIGHT(A1,2)/60
result: 781680.95
Biff
"Nauj Solrac" wrote in message
...
Hi, I've been working for long time with this formula but now, some
times,
need to enter values greater tha 9999:59, i.e.
A1=32570:57
B1=A1*24 -- I got "#VALUE!" as a result
Is there any formula to get the decimal amount when I enter manually
32570:57; Please let me know
Thanks
'Nauj Solrac'
"Frank Kabel" wrote:
Hi
if this time value is in A1 enter the following formula
=A1*24
and format this cell as 'Number'
--
Regards
Frank Kabel
Frankfurt, Germany
CRAIG.JOHNSON wrote:
Wish modify my time card worksheet to display weekly totals in a
decimal Hour number only (instead of the current
Hour:Minute:Second)
format (i.e., would like to convert 43 HR: 30 MN: 00 SEC to 43.5
HR).
Have looked at the TIME and TIME VALUE functions and they do not
appear to do what I would like.
|