| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Round/Display (HR:MN:SEC) data in decimal HR format for Time C
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round/Display (HR:MN:SEC) data in decimal HR format for Time C
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round/Display (HR:MN:SEC) data in decimal HR format for Time C
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round/Display (HR:MN:SEC) data in decimal HR format for Time C
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round/Display (HR:MN:SEC) data in decimal HR format for Time C
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round/Display (HR:MN:SEC) data in decimal HR format for Time C
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro question | Excel Worksheet Functions | |||
| unable to change date format on imported data | Excel Discussion (Misc queries) | |||
| Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
| Cell data format | Excel Discussion (Misc queries) | |||
| " / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
Linear Mode
