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

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

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.

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

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.

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.

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.