Problem in date format in excel

[anurags]

Hi,

I have just downloaded the excel file from one of the tool. Its strange that excel is picking up different date formats while exporting.
Have tried to format the cells but its not working.
Can you please help me to get format as dd/mm/yyyy hh:mm.

Somewhere its showing AM, PM, somewhere just date with time stamp.

Attaching the excel that I am working on.

Thanks in Advance.

[Richard Buttrey]

Hi

I'd be inclined to use a helper column given that your download is not consistent, sometimes being a date/time number and sometimes text.
In E2, (formatted as dd/mm/yy hh:mm) and copied down enter

=VALUE(IF(ISNUMBER(D2),D2,LEFT(D2,14)))

[Marcol]

Try this
Select Column D
Data > Text to columns ...

Delimited > Next > Next ... Column data format:= Select the radio button "Date" and DMY from the drop-down > Click "Finish"

Does that solve your problem?

[anurags]

Thansk Richard,but its not working,
I am getting:-

S no Record Type Status Open Time
1 REQUEST Work in Progress 26/01/12 17:43:28 #VALUE!
2 REQUEST Work in Progress 29/03/12 11:12:59 #VALUE!
3 REQUEST Work in Progress 28/05/12 14:33:18 #VALUE!
4 REQUEST Work in Progress 06/07/12 15:03 41096.62715
5 REQUEST Work in Progress 26/06/12 16:21:01 #VALUE!
6 REQUEST Work in Progress 26/06/12 16:29:00 #VALUE!
7 REQUEST Work in Progress 28/06/12 11:57:00 #VALUE!
8 REQUEST Work in Progress 28/06/12 12:08:51 #VALUE!
9 REQUEST Work in Progress 28/06/12 12:35:10 #VALUE!
10 REQUEST Work in Progress 29/06/12 18:52:48 #VALUE!
11 REQUEST Work in Progress 07/04/12 12:09 41006.50644
12 REQUEST Work in Progress 07/05/12 12:26 41036.51861
13 REQUEST Work in Progress 07/05/12 14:36 41036.60899
14 REQUEST Work in Progress 07/05/12 14:47 41036.61661

[anurags]

Thanks Marcol, I tried this earlier , but its not changing the format.
I have attached the excel file on which I am working. Is it working for you?

[Richard Buttrey]

Don't know why you get that since you're picking up column C which is not mentioned in my formula.

Marcol's solution is more efficient but if you still want to see why the formula isn't working see the attached.

[anurags]

Dear Richard, I uploaded the workbook with my first post. Are you able to find it or should I upload it again?

[Richard Buttrey]

We've just crosses in the aether. See my attachment in post #6

Marcol's solution also works so I'm not clear why you couldn't get it to do so.

[anurags]

Dear Richard,
Please find attached the file. I just copy pasted, but its asking for value
Kindly let me know what I am doing wrong. I have chosen column D

[Richard Buttrey]

Not sure what you mean by 'its asking for value'. What's 'it' mean?

I can see that column E has '##########' but that's only because the column isn't wide enough. Is that what you mean?

[anurags]

please see below for the output.

1 REQUEST Work in Progress 26/01/12 17:43:28 #VALUE!
2 REQUEST Work in Progress 29/03/12 11:12:59 #VALUE!
3 REQUEST Work in Progress 28/05/12 14:33:18 #VALUE!

By Value I meant #VALUE!

Is this because I am using excel 2003 and not the higher version?

[anurags]

please see below for the output.

1 REQUEST Work in Progress 26/01/12 17:43:28 #VALUE!
2 REQUEST Work in Progress 29/03/12 11:12:59 #VALUE!
3 REQUEST Work in Progress 28/05/12 14:33:18 #VALUE!

By Value I meant #VALUE!

Is this because I am using excel 2003 and not the higher version?

[Marcol]

The only thing I can see is that the result is not showing seconds, but then you did ask this

Can you please help me to get format as dd/mm/yyyy hh:mm.

What do you mean by this? Where does it happen in the sample provided?

Somewhere its showing AM, PM, somewhere just date with time stamp.

If you have text values like "06/07/2012 15:03:06 PM" the suggested methods will fail

Try in E2

Please Login or Register  to view this content.

Drag/Fill Down

P.S. If there are no strings with AM or PM this should work.

Please Login or Register  to view this content.

[Richard Buttrey]

Sorry, still can't see that. I'm using the file you attached to post #9 and it shows the dates & times in column E as expected in 2003, 2007 & 2010.