Formatting Crisis

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EddieP - 26 Feb 2005 01:09 GMT

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?

Reply to this Message

JE McGimpsey - 26 Feb 2005 01:33 GMT

> 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
[quoted text clipped - 3 lines]
> "=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.

Reply to this Message

EddieP - 26 Feb 2005 02:12 GMT

> > 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
[quoted text clipped - 14 lines]
>
> 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?

Reply to this Message

EddieP - 26 Feb 2005 02:15 GMT

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?

Reply to this Message

JE McGimpsey - 26 Feb 2005 05:01 GMT

> 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 "######."
[quoted text clipped - 3 lines]
>
> Any suggestions?

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

Reply to this Message

EddieP - 26 Feb 2005 14:33 GMT

> > 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 "######."
[quoted text clipped - 5 lines]
>
> 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

Reply to this Message

JE McGimpsey - 26 Feb 2005 15:00 GMT

> 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.

Reply to this Message

EddieP - 26 Feb 2005 17:01 GMT

> > 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.
[quoted text clipped - 3 lines]
>
> 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

Reply to this Message

Mac Forum / Applications / Excel / February 2005

Formatting Crisis