date and number conversion

F

Faeandar

I have 3 columns: date numbers numbers

The date column has the format:

Thu Sep 1 17:00:00 2005

I tried selecting the column, format cells, category: date, and select
my preference but nothing changes. Essentially I want to remove the
time and change the date format to D/M/Y. i also tried this on an
individual cell.

For the number columns they are in bytes and I want to change them to
gigs. is there a clicky to do that?

Thanks.

~F
 
P

Pete_UK

It sounds as if the "date" has come in as text, so no amount of
formatting will change this - you could use a helper cell to split it
using LEFT, RIGHT and MID and then join it up again using &"/"& etc
with VALUE around, or using the DATE function.

As for bytes to gigs, the conversion factor is 1024 for a thousand (2
to the power 10), so you will need to divide by this three times, i.e.:

=B1/1024/1024/1024

if your bytes are in B1.

Hope this helps.

Pete
 
F

Faeandar

It sounds as if the "date" has come in as text, so no amount of
formatting will change this - you could use a helper cell to split it
using LEFT, RIGHT and MID and then join it up again using &"/"& etc
with VALUE around, or using the DATE function.

As for bytes to gigs, the conversion factor is 1024 for a thousand (2
to the power 10), so you will need to divide by this three times, i.e.:

=B1/1024/1024/1024

if your bytes are in B1.
Click to expand...

And if I have a column wtih a hundred cells? Is there a column-based
formula option?

Thanks.

~F
 
B

Beege

Faeandar said:
And if I have a column wtih a hundred cells? Is there a column-based
formula option?

Thanks.

~F
Click to expand...

If you put =1/1024/1024/1024 in one cell, copy "byte" column, paste
special/mutliply, select the cell with the calculated denominator, OK, the
byte column should reflect "gigs" instead of "bytes"

Beege
 
F

Faeandar

If you put =1/1024/1024/1024 in one cell, copy "byte" column, paste
special/mutliply, select the cell with the calculated denominator, OK, the
byte column should reflect "gigs" instead of "bytes"

Beege
Click to expand...

I'm afraid none of that makes sense to me. I tried a few things that
I *thought* were what you described but none worked.

~F
 
P

Pete_UK

You could copy the formula I gave you, i.e.:

=B1/1024/1024/1024

down the column to convert all the "byte" values in column B. The
values can be "fixed" (i.e. highlight the cells, click <copy> then Edit
| Paste Special | Values (check) then OK and <Enter>). These fixed
values can then be copied over the original values you had in column B.

The method Beege gave you does all this in one operation. Enter
=1/1024/1024/1024 in a blank cell somewhere then select the cell and
click <copy>. Then highlight the cells where your Byte values are (B in
my example above), and click Edit | Paste Special | Multiply (check)
then OK and <Esc>.

Hope this helps.

Pete
 
Top