Formatting Crisis

[EddieP]

Hey all, got a problem formatting cells. I have columns and columns of
time data in the following format - 1000:30:00, meaning "one thousand
minutes, thirty seconds" of accrued time. For the calculations I need
to make, I have to currently go through each cell and manually change
the time format to decimal format by entering, for example,
"=1000+(30/60)" to get the number I need - 1000.5. I know there has to
be an easier way but I can't figure it out. Any help?

 

[JE McGimpsey]

Hey all, got a problem formatting cells. I have columns and columns of
time data in the following format - 1000:30:00, meaning "one thousand
minutes, thirty seconds" of accrued time. For the calculations I need
to make, I have to currently go through each cell and manually change
the time format to decimal format by entering, for example,
"=1000+(30/60)" to get the number I need - 1000.5. I know there has to
be an easier way but I can't figure it out. Any help?

XL interprets 1000:30:00 as 1000 hours, 30 minutes. Times in XL are
stored as fractional days, so 1 hour = 1/24 = 0.0416666666666667.

To convert your times, multiply by 24.

=A1*24

and format as General or another number format.

If you want to do it in place, put 24 in a blank cell. Copy the cell.
Select your times. Choose Edit/Paste Special, selecting the Values and
Multiply radio buttons. Click OK. Format the cells as General.

 

[EddieP]

XL interprets 1000:30:00 as 1000 hours, 30 minutes. Times in XL are
stored as fractional days, so 1 hour = 1/24 = 0.0416666666666667.

To convert your times, multiply by 24.

=A1*24

and format as General or another number format.

If you want to do it in place, put 24 in a blank cell. Copy the cell.

Select your times. Choose Edit/Paste Special, selecting the Values and
Multiply radio buttons. Click OK. Format the cells as General.

I'm not sure I follow. I tried that method, but could not get the
answer I need. When I multiply by 24, the cell shows "######."

I set up a random cell with "100:30:00," meaning I need a final answer
of "100.5." I multiplied by 24 and got "######."

Any suggestions?

 

[EddieP]

I'm not sure I follow. I tried that method, but could not get the
answer I need. When I multiply by 24, the cell shows "######" for the
larger values (i.e. 100:30:00). It works fine for the smaller values,
however, but the majority of my cells are giving me "######."

I set up a random cell with "100:30:00," meaning I need a final answer
of "100.5." I multiplied by 24 and got "######." Any suggestions?

 

[JE McGimpsey]

I'm not sure I follow. I tried that method, but could not get the
answer I need. When I multiply by 24, the cell shows "######."

I set up a random cell with "100:30:00," meaning I need a final answer
of "100.5." I multiplied by 24 and got "######."

Any suggestions?

Did you format as General? If so, make your columns a little wider.

 

[EddieP]

Did you format as General? If so, make your columns a little wider.

JE,

Thanks a LOT for your help. I'm almost there. Using your method I've
been able to convert many of the values I have. One problem remains.
It looks as if any times greater than three digits (i.e. 1000:30) are
giving me the result "#VALUE!." If I can just get these fixed, I'm
good to go. Any last suggestions??

Thanks again for all your help. You have no idea how much easier
you've made my life!

Ed

 

[JE McGimpsey]

Thanks a LOT for your help. I'm almost there. Using your method I've
been able to convert many of the values I have. One problem remains.
It looks as if any times greater than three digits (i.e. 1000:30) are
giving me the result "#VALUE!." If I can just get these fixed, I'm
good to go. Any last suggestions??

Hmmmm.. I can use either technique at least up to 9999:59:59 (the max
value recognized as a Time by XL's entry parser), and trying it with a
calculated value of 9999000:00:00 worked too.

If your entries are more than 9999 hours, they'll be interpreted as Text
and multiplying text with = A1 * 24 will return the #VALUE! error.

 

[EddieP]

Hmmmm.. I can use either technique at least up to 9999:59:59 (the max

value recognized as a Time by XL's entry parser), and trying it with a
calculated value of 9999000:00:00 worked too.

If your entries are more than 9999 hours, they'll be interpreted as Text
and multiplying text with = A1 * 24 will return the #VALUE! error.

GOT IT! For some reason the presence of the comma was throwing
everything off. Thanks so much for your help JE. Life is good!!!

Ed