Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Time in Formulas
We often get reports where the time is entered as 41:58:00 where the 41 is
hours, the 58 is minutes and the 00 is seconds. However, this data is copy and pasted from another form. It is formatted as: CUSTOM [h]:mm:ss. However, when you click on the cell, what you see in the formula line is 1/1/1900 5:58:00 PM How do I use this time to convert to seconds (if necessary to get my final per hour answer)? I then need to divide it by the number of papers completed within that time frame. Say a rep has completed 314 papers in that length of time. I need to show how many papers were completed per hour. I know this is probably simple to one of you out there, but it has me totally confused. Help!!! Thanks, Cheri |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Time in Formulas
Hi Cheri,
In Excel. time is stored as a fraction of a day. To get the # of hours, multiply by 24 and format as general or Number, not as Time (which is what Excel does automatically) -- Kind regards, Niek Otten Microsoft MVP - Excel "Cheri" wrote in message ... | We often get reports where the time is entered as 41:58:00 where the 41 is | hours, the 58 is minutes and the 00 is seconds. However, this data is copy | and pasted from another form. It is formatted as: CUSTOM [h]:mm:ss. | However, when you click on the cell, what you see in the formula line is | 1/1/1900 5:58:00 PM | | How do I use this time to convert to seconds (if necessary to get my final | per hour answer)? I then need to divide it by the number of papers completed | within that time frame. Say a rep has completed 314 papers in that length of | time. I need to show how many papers were completed per hour. | | I know this is probably simple to one of you out there, but it has me | totally confused. Help!!! | | Thanks, | Cheri |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Time in Formulas
Cheri wrote:
We often get reports where the time is entered as 41:58:00 where the 41 is hours, the 58 is minutes and the 00 is seconds. [....] It is formatted as: CUSTOM [h]:mm:ss. However, when you click on the cell, what you see in the formula line is 1/1/1900 5:58:00 PM How do I use this time to convert to seconds (if necessary to get my final per hour answer)? Time is stored as days since 1/1/1900, which explains the "funny" form that you see in the fx (formula) field. You can see the true form by formatting as Number. So 41:58:00 is approximately the number 1.7486. I then need to divide it by the number of papers completed within that time frame. Say a rep has completed 314 papers in that length of time. I need to show how many papers were completed per hour. If A1 is the number of papers and B1 is the time, the following is papers per hour: =A1 / (24 * B1) That is equivalent to: =A1 / B1 / 24 Caveat: You might need to explicitly format that cell as General or Number. but it has me totally confused. I can understand why. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Time in Formulas
Great! Thanks!!!!
"Niek Otten" wrote: Hi Cheri, In Excel. time is stored as a fraction of a day. To get the # of hours, multiply by 24 and format as general or Number, not as Time (which is what Excel does automatically) -- Kind regards, Niek Otten Microsoft MVP - Excel "Cheri" wrote in message ... | We often get reports where the time is entered as 41:58:00 where the 41 is | hours, the 58 is minutes and the 00 is seconds. However, this data is copy | and pasted from another form. It is formatted as: CUSTOM [h]:mm:ss. | However, when you click on the cell, what you see in the formula line is | 1/1/1900 5:58:00 PM | | How do I use this time to convert to seconds (if necessary to get my final | per hour answer)? I then need to divide it by the number of papers completed | within that time frame. Say a rep has completed 314 papers in that length of | time. I need to show how many papers were completed per hour. | | I know this is probably simple to one of you out there, but it has me | totally confused. Help!!! | | Thanks, | Cheri |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Time in Formulas
That worked perfectly! Thanks so much!!!
" wrote: Cheri wrote: We often get reports where the time is entered as 41:58:00 where the 41 is hours, the 58 is minutes and the 00 is seconds. [....] It is formatted as: CUSTOM [h]:mm:ss. However, when you click on the cell, what you see in the formula line is 1/1/1900 5:58:00 PM How do I use this time to convert to seconds (if necessary to get my final per hour answer)? Time is stored as days since 1/1/1900, which explains the "funny" form that you see in the fx (formula) field. You can see the true form by formatting as Number. So 41:58:00 is approximately the number 1.7486. I then need to divide it by the number of papers completed within that time frame. Say a rep has completed 314 papers in that length of time. I need to show how many papers were completed per hour. If A1 is the number of papers and B1 is the time, the following is papers per hour: =A1 / (24 * B1) That is equivalent to: =A1 / B1 / 24 Caveat: You might need to explicitly format that cell as General or Number. but it has me totally confused. I can understand why. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Time in Formulas
Errata....
I wrote: Cheri wrote: We often get reports where the time is entered as 41:58:00 where the 41 is hours, the 58 is minutes and the 00 is seconds. [....] It is formatted as: CUSTOM [h]:mm:ss. However, when you click on the cell, what you see in the formula line is 1/1/1900 5:58:00 PM [....] Time is stored as days since 1/1/1900, which explains the "funny" form that you see in the fx (formula) field. Poorly phrased. I meant to say that time is __interpreted__ as days since 1/1/1900, just like a date serial numbers. But as I explained further.... You can see the true form by formatting as Number. So 41:58:00 is approximately the number 1.7486. That is, time is __stored__ in days and fractional parts thereof. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Time in Formulas
Okay...now you are way tooooo much like me!!!! No Errata was needed :o) I
totally understood and I am delighted with the results and your help!!!!!! Thanks again! Cheri " wrote: Errata.... I wrote: Cheri wrote: We often get reports where the time is entered as 41:58:00 where the 41 is hours, the 58 is minutes and the 00 is seconds. [....] It is formatted as: CUSTOM [h]:mm:ss. However, when you click on the cell, what you see in the formula line is 1/1/1900 5:58:00 PM [....] Time is stored as days since 1/1/1900, which explains the "funny" form that you see in the fx (formula) field. Poorly phrased. I meant to say that time is __interpreted__ as days since 1/1/1900, just like a date serial numbers. But as I explained further.... You can see the true form by formatting as Number. So 41:58:00 is approximately the number 1.7486. That is, time is __stored__ in days and fractional parts thereof. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time clock formulas | Excel Discussion (Misc queries) | |||
How do I copy formulas but using the same range each time I copy | Excel Worksheet Functions | |||
formula to determine time range overlap? | Excel Discussion (Misc queries) | |||
Can series tool change formulas over time when used to copy them | Excel Worksheet Functions | |||
formulas for employee time sheets | Excel Worksheet Functions |