date and number conversion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Faeandar - 31 Aug 2006 22:53 GMT

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

Reply to this Message

Pete_UK - 01 Sep 2006 01:42 GMT

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

> I have 3 columns: date numbers numbers
>
[quoted text clipped - 13 lines]
>
> ~F

Reply to this Message

Faeandar - 01 Sep 2006 19:11 GMT

>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
[quoted text clipped - 7 lines]
>
>if your bytes are in B1.

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

Thanks.

~F

>Hope this helps.
>
[quoted text clipped - 17 lines]
>>
>> ~F

Reply to this Message

Beege - 01 Sep 2006 19:27 GMT

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

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

Reply to this Message

Faeandar - 02 Sep 2006 01:47 GMT

>>>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
[quoted text clipped - 20 lines]
>
>Beege

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

Reply to this Message

Pete_UK - 03 Sep 2006 01:44 GMT

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

> >>>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
[quoted text clipped - 25 lines]
>
> ~F

Reply to this Message