Help with Loops is my guess

[York]

Hi,

I have exported some data into a spreadsheet and the column I am having
difficulty with is the column that shows dates and times e.g. 1/31/2006
01:36:21PM.

Now what I need to do is:
1) take out the time and be left with the date (I have this formula)
=LEFT(A1,LEN(A1)-12)

2) change the date into UK format instead of US format (I have this
formula) =DATE(RIGHT(P8,2)+2000,LEFT(P8,2),MID(P8,4,2))

Both these work, but I needed them to both to work at the same time:

From: 1/31/2006 01:36:21PM.
To: 31/01/2006

Can anyone help. Thanking you in advance.

Kim
x

 

[Guest]

1) Instead of =LEFT(A1,LEN(A1)-12) use the formula =INT(A1)
2) a) RECOMMENDED because it keeps the data as a date
- change the formatting of the cell.
- Right-click on the cell with the formula =INT(A1)
- Select 'Format Cells...'
- Select 'Custom'. It is at the bottom of the 'Category:' list
- In the Text Box underneath 'Type:', enter...
dd/mm/yyyy
- Select OK
b) NOT Recommended because it changes the data to text
- Instead of the formula =INT(A1)
change it to =TEXT(a1,"dd/mm/yyyy")
HTH,

 

[Guest]

Just re-read your post and realized you have imported Date/Time as text
instead of values.
That changes everything. Sorry.
The formula should simply be...
=DATEVALUE(A1)
and you should change the formatting of the cell.
- Right-click on the cell with the formula =DATEVALUE(A1)
- Select 'Format Cells...'
- Select 'Custom'. It is at the bottom of the 'Category:' list
- In the Text Box underneath 'Type:', enter...
dd/mm/yyyy
- Select OK
HTH,