Using Time in Formulas

[Guest]

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

 

[Niek Otten]

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

| 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

 

[joeu2004]

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.

 

[Guest]

Great! Thanks!!!!

 

[Guest]

That worked perfectly! Thanks so much!!!

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.

 

[joeu2004]

Errata....

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.

 

[Guest]

Okay...now you are way tooooo much like me!!!! No Errata was needed ) I
totally understood and I am delighted with the results and your help!!!!!!

Thanks again!
Cheri

Errata....

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.