Hi, Guys

I really need help on this. I've been looking for an answer for a LONG time but couldn't find it. In my work, I need to sort a table on Excel using the date present on every line. The problem is that the spreadsheet I use have different layouts of dates as seen below.

2/13/2017 4:43:02 AM (MM/DD/YYYY)
02/11/2017 05:05 (DD/MM/YYYY)

I can't sort it this way because it always sort it wrongly.
What I need to do is to split cells and then concatenate them again but this is causing a lot of trouble.




Could you help me check if there's any way to do it automatically, using a macro, or at least using just a formula?

Thanks =)
Leo

You could use this to extract out the date

=TRIM(LEFT(A1,FIND(" ",A1)))+0

Sorry, I'm not sure I get it.

Here's what I have:

A1 = 2/14/2017 11:14:58 AM
A2 = 02/12/2017 12:40

Obs. I have 50 different lines, these are just examples

Can you explain to me how to do it?

Tks

Actually, now that I think about it, you can use Int and Mod to separate the date and time.

See if this attachment helps

Attached Files

Leonardo.xlsx (8.7 KB, 7 views)

Thanks for your time.

Unfortunately, it is not working on dates mm/dd/aaaa.

12/02/2017 12:40 12/fev/17 12:40 PM
2/14/2017 11:14:58 #VALUE! #VALUE!
02/12/2017 12:40 02/dez/17 12:40 PM


I think the problem is that I need to convert some dates from mm/dd/aaaa in order to let all dates equal dd/mm/aaaa =/


Tks

Actually, part of the problem is your not giving enough samples to know the extent of your data. What was offered in post #4 works with the sample posted in post #3.

Please attach a good Excel worksheet example of all the possible scenarios contained within your data.

Sorry.


Here it is.
On the tab Example, it is the examples os dates I have. What I need to to is to standardize them all into DD/MM/AAAA HH/MM in order to allow me sort them.


Thanks

Attached Files

Example.xlsx (19.3 KB, 9 views)

For some reason, your dates in column A are not being read as dates. To see this, in B2 use =ISNUMBER(A2)

You should get FALSE as it's not a number, so we have to convert it to a number.

A couple of ways to do this:

1) In B2 use the formula =A2+0 and copy it down. Now convert column B to values and sort. The +0 forces A2 into a number.

2) Use Text to Columns

Text to Columns

• Highlight range to convert >> Column A
• Data >> Data Tools >> Text to Columns >> Check Delimited >> Next >> Next
• Check Date and with the drop down select your format
• Finish

Attached Files

Example (2).xlsx (21.0 KB, 6 views)