0

I'm trying to split my time and date (numbers) from one column into three columns but am getting zeros when attempting this by treating them as General format.

  • My time column: 14:22:13
  • My date column: 26/3/2013

When I select delimiter as ':' then click finish I get 00:00:00 as my time over the three columns:

00:00:00 | 00:00:00 | 00:00:00

instead of:

14 | 22 | 13
pnuts
  • 58,317
  • 11
  • 87
  • 139
user1574598
  • 3,771
  • 7
  • 44
  • 67

1 Answers1

0

It seems like cell formatting will not really make this work as you would expect. You might have to resort to using formulas to achieve what you want.

If formulas are acceptable try this

enter image description here

datatoo
  • 2,019
  • 2
  • 21
  • 28
  • Thanks, would the date use 3 functions: `=day(A1:AN), =month(A1:AN), =year(A1:AN)`? – user1574598 Nov 13 '13 at 12:22
  • Yes that will work. Realize of course that a US date format is normally mm/dd/yy so 26/3/2013 is not a possible date but if your region is not the US it will work correctly – datatoo Nov 13 '13 at 20:21