Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Loops is my guess
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Loops is my guess
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, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Gary L Brown" wrote: 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, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "York" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IRR Guess | Excel Worksheet Functions | |||
Guess my number game for kids. | Excel Worksheet Functions | |||
Excel Woes - Formula bug (I guess) | Excel Worksheet Functions | |||
IRR Guess Problem | Excel Worksheet Functions |