Date/Time Excel Problem

hallelujah

Hi all,

I have an excel sheet with a date/time column. I want to split this column up into 2 columns. One for date and the other for time.

Do any of you know how to do this? When I right click on one of the columns and click Format, then I try changing the column to either the date of time, the column doesn't change...

Any ideas?

Thanks,
Jeff

--amadeus--

Do you want to change the data (ie split the cell contents) or just change the displayed values?

hallelujah

I have one column that has a date/time. I want to record a macro that copies all of the contents of one excel sheet and places it into another sheet and for the date/time column, I want to split it into 2 columns, one for date the other for time.

E.g.

Before:
Date/Time
01/01/2009 12:30:11

After:
Date
01/01/2009

Time
12:30:11

Seth Brundle

Column A = recorded date & time formatted as date.
Column B uses function (=B2) and formats as time

hallelujah

kbannon wrote: »

Column A = recorded date & time formatted as date.
Column B uses function (=B2) and formats as time

That is what I am trying to do but it doesn't work.. I go in and format the date/time column, hit Ok and nothing changes.

Hagar

cell a1 =now() ie full date and time
cell a2 =a1 then format cell as date
cell a3 =a1 then format cell as time

the formatting of the display cell is the important bit.

HTH

Seth Brundle

The solution I suggested works fine for me here!

Hagar

Is your original cell a text value? I suspect it is.
If so you need to do a bit of string manipulation to extract the date part and the time part.

Hagar

Alternative

Try this using details from my post above.

d2=text(a1,"dd/mm/yyyy hh:mm:ss") that gives a text field which you can manipulate, you can hide this column if you wish

Required Date Column =left(d2,10)
Required Time Column =right(d2,8)

hallelujah

Hagar wrote: »

Alternative

Try this using details from my post above.

d2=text(a1,"dd/mm/yyyy hh:mm:ss") that gives a text field which you can manipulate, you can hide this column if you wish

Required Date Column =left(d2,10)
Required Time Culumn =right(d2,8)

The original date/time column is in 'General' format and it isn't changing when I try. This seems to work:

text(a1,"dd/mm/yyyy hh:mm:ss")

I see a1 is hard coded. How do I amend this so that all rows use formula in my new colum? say A1 - Ax?

Hagar

hallelujah wrote: »

The original date/time column is in 'General' format and it isn't changing when I try. This seems to work:

text(a1,"dd/mm/yyyy hh:mm:ss")

I see a1 is hard coded. How do I amend this so that all rows use formula in my new colum? say A1 - Ax?

Highlight the cell that contains text(a1,"dd/mm/yyyy hh:mm:ss") and grab the little black box on the lower right hand corner of the cell by right clicking and hold down the button. Drag the box downwards as far as you need and the formula will change automatically to reference a2,a3,a3 etc.