Date & time question
 

Hi,

I have a spreadsheet that has the date & time in the same cell, in the
format:

dd/mm/yy hh:mm

Is there a method of easily separating the date and time into two
different columns? Unfortunately there are over 300 hundred cells in a
column that need to be sorted.

--
Cheers

Peter

Please remove the invalid to reply

Date & time question
 

Hi Peter,

If your cells are in column A,
in column B : =Int(a1) Format Cell dd/mm/yy
in column C : =A1-B1 Format Cell hh:mm

HTH
Cheers
Carim

Date & time question
 

On 2 Oct 2006 05:02:39 -0700, "Carim" wrote:

Hi Peter,

If your cells are in column A,
in column B : =Int(a1) Format Cell dd/mm/yy
in column C : =A1-B1 Format Cell hh:mm

HTH
Cheers
Carim

Hi Carim,

Thanks for your suggestion. I've entered =Int(a1) into cells in col B,
and formated the column as dd/mm/yy, but no joy - I get the #Value
error messgae - guess I must be doing something wrong.

--
Cheers

Peter

Please remove the invalid to reply

Date & time question
 

Hi Peter,

Are you sure your dateTime is a numeric value and not a string
representation. If you're lucky this might work -

=INT(TRIM(A7))

otherwise post back what you debug with this -
Debug.Print ActiveCell.Value, ActiveCell.Value2

embrace each with quotes to indicate any leading/trailing spaces
also indicate the full cell contents as displayed

Regards,
Peter T
"Peter" wrote in message
...
On 2 Oct 2006 05:02:39 -0700, "Carim" wrote:

Hi Peter,

If your cells are in column A,
in column B : =Int(a1) Format Cell dd/mm/yy
in column C : =A1-B1 Format Cell hh:mm

HTH
Cheers
Carim

Hi Carim,

Thanks for your suggestion. I've entered =Int(a1) into cells in col B,
and formated the column as dd/mm/yy, but no joy - I get the #Value
error messgae - guess I must be doing something wrong.

--
Cheers

Peter

Please remove the invalid to reply

Date & time question
 

On Mon, 2 Oct 2006 13:59:42 +0100, "Peter T" <peter_t@discussions
wrote:

Hi Peter,

Are you sure your dateTime is a numeric value and not a string
representation. If you're lucky this might work -

=INT(TRIM(A7))

otherwise post back what you debug with this -
Debug.Print ActiveCell.Value, ActiveCell.Value2

embrace each with quotes to indicate any leading/trailing spaces
also indicate the full cell contents as displayed

Regards,
Peter T

Hi Peter t,

The cell is shown as having a custom format, dd/mm/yyyy hh:mm - if I
format the cell as a number then for the cell entry:

01/10/2006 21:17

I get the following numeric:

38991.8868055556

Not too sure what do do with "Debug.Print ActiveCell.Value,
ActiveCell.Value2" :-(

--
Cheers

Peter

Please remove the invalid to reply

Date & time question
 

"Peter" wrote in message
...
On Mon, 2 Oct 2006 13:59:42 +0100, "Peter T" <peter_t@discussions
wrote:

Hi Peter,

Are you sure your dateTime is a numeric value and not a string
representation. If you're lucky this might work -

=INT(TRIM(A7))

otherwise post back what you debug with this -
Debug.Print ActiveCell.Value, ActiveCell.Value2

embrace each with quotes to indicate any leading/trailing spaces
also indicate the full cell contents as displayed

Regards,
Peter T

Hi Peter t,

The cell is shown as having a custom format, dd/mm/yyyy hh:mm - if I
format the cell as a number then for the cell entry:

01/10/2006 21:17

I get the following numeric:

38991.8868055556

Not too sure what do do with "Debug.Print ActiveCell.Value,
ActiveCell.Value2" :-(

--
Cheers

Peter


I pasted 38991.8868055556 in A1 then using Carim's formulas I got
01-Oct-06 and 21:17 in B1 & C1 respectively. Not sure why you are getting
the #Value error.

Regards,
Peter T