Frustrating conversion to a time and date

Hi there,

So I'm using Excel for Mac v15 and I have this CSV with the dates a little mixed up. I'm trying to make them all uniform AND separate date from time. So, as you can imagine, I've used Text-to-columns and spent about 4 hours so far to try to get this sorted.

Of course, not happening. Somewhere along the line it worked, but did the following:
Date has date, but also 00:00 in the end like 01/04/2017 0:00. And the time if it was 13.01, it becomes 01:01 (totally flipping the am/pm)

Does anyone have a solution?

Screen Shot 2017-06-20 at 2.23.29 PM.png

Check out this thread: https://www.excelforum.com/excel-gen...e-columns.html

Hi all- Could you be more specific about what you're trying to achieve?
1) Do you want to convert the text to NUMBERS or just re-format as TEXT.
2) In either case, what do you want the output to look like EXACTLY?

Originally Posted by leelnich

Hi all- Could you be more specific about what you're trying to achieve?
1) Do you want to convert the text to NUMBERS or just re-format as TEXT.
2) In either case, what do you want the output to look like EXACTLY?

Want it to look like the way it is in green boxes.
Screen_Shot_2017-06-20_at_3_17_02_PM.png

Originally Posted by LeoTaxi

Or

in B2

Please Login or Register  to view this content.

in C2

Please Login or Register  to view this content.

Kind regards
Leo

From thread referenced above which was created yesterday!

Originally Posted by Raphaelp

From thread referenced above which was created yesterday!

Hi,

I was just replying to you to say how that solution hasn't worked.
Here you go:


Screen Shot 2017-06-20 at 3.24.49 PM.png

Try these:

B2 =IFERROR(DATEVALUE(LEFT(A2,10)),INT(A2))
C2 =IFERROR(TIMEVALUE(RIGHT(A2,5)),MOD(A2,1))

Formatted appropriately.

Originally Posted by 63falcondude

Try these:

B2 =IFERROR(DATEVALUE(LEFT(A2,10)),INT(A2))
C2 =IFERROR(TIMEVALUE(RIGHT(A2,5)),MOD(A2,1))

Formatted appropriately.

Wow, time worked!! Thanks!!
But date is still patchy :/

Screen_Shot_2017-06-20_at_3_17_02_PM.png

Originally Posted by Yourname

Wow, time worked!! Thanks!! But date is still patchy :/

It is working on my end. Might have something to do with the different regional settings.

Can you upload a copy of your workbook? This should help us figure out what is going on better than a picture.

Originally Posted by 63falcondude

It is working on my end. Might have something to do with the different regional settings.

Can you upload a copy of your workbook? This should help us figure out what is going on better than a picture.

Here you go.

Umm, not sure why but when I click the attach icon, it doesn't drop down...

Hope it's here now

Originally Posted by Arkadi

For the date:

Assuming the first 2 numbers ("01") are the month try:

Formula:

Please Login or Register  to view this content.

Thanks, but that worked for one part, but not the other:

Screen_Shot_2017-06-20_at_4_07_36_PM.png

Originally Posted by Arkadi

Yeah forgot to account for the format that does work... how about:

Formula:

Please Login or Register  to view this content.

LOL this one has a whole different result...

Screen_Shot_2017-06-20_at_4_21_00_PM.png

Try this in B2

=IFERROR(DATEVALUE(LEFT(A2,10)),DATE(YEAR(A2),DAY(A2),MONTH(A2)))

If that does not produce the desired results, what does =MONTH(A5) return?

The large formula didn't work, and =MONTH(A5) returned 4.

What do you mean by "didn't work"? After you entered the first formula from post #17 into B2, what did it return?

In the attached workbook, I put the following formulas in B2 and C2:

B2 =IFERROR(DATEVALUE(LEFT(A2,10)),DATE(YEAR(A2),DAY(A2),MONTH(A2)))
C2 =IFERROR(TIMEVALUE(RIGHT(A2,5)),MOD(A2,1))

Originally Posted by 63falcondude

What do you mean by "didn't work"? After you entered the first formula from post #17 into B2, what did it return?

In the attached workbook, I put the following formulas in B2 and C2:

B2 =IFERROR(DATEVALUE(LEFT(A2,10)),DATE(YEAR(A2),DAY(A2),MONTH(A2)))
C2 =IFERROR(TIMEVALUE(RIGHT(A2,5)),MOD(A2,1))

Sorry, I mean it gave that error:


Screen_Shot_2017-06-20_at_9_27_16_PM.png

Super weird, but I opened your workbook and:


Screen Shot 2017-06-20 at 9.28.57 PM.png

That is so weird. It works fine on my end. One more shot

B2 =IF(ISTEXT(A2),DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2)),DATE(YEAR(A2),DAY(A2),MONTH(A2)))

Formatted as a date.

Originally Posted by 63falcondude

That is so weird. It works fine on my end. One more shot

B2 =IF(ISTEXT(A2),DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2)),DATE(YEAR(A2),DAY(A2),MONTH(A2)))

Formatted as a date.

WOW you guys are simply the best!!!! It worked!!! Thanks a super tonnnnn!!!!!


Screen_Shot_2017-06-20_at_10_10_29_PM.png


But I copied the formula all the way to the bottom and noticed that the rows that had the SAME format as A2/3/4 are still not converting:


Screen_Shot_2017-06-20_at_10_14_27_PM.png

Originally Posted by AlKey

This should be pretty simple
Enter formula in B2 to extract Date

Formula:

Please Login or Register  to view this content.

Enter formula in C2 to extract Time

Formula:

Please Login or Register  to view this content.

Format cells B2 as Date and C2 as Custom, h:mm and copy both formulas down
Please see attached.

Thanks much for your reply!
Unfortunately, that didn't work either.

The previous formula given by 63falcondude worked, and worked for most of the column but stopped short. So I tried yours, and it didn't work on any of the fields. I'm telling you Excel hates me even when so many of you gurus are so generously taking time out to help me



Screen Shot 2017-06-21 at 8.38.31 AM.png

I know this has become super frustrating for all you gurus... but pleeassseee don't give up. You're my only hope lol

I was out yesterday... why don't you just select column A and press ctrl+H. Find what:, [comma] Replace with:[leave this blank], then click "Replace All".

Then do as I wrote above and see what happens:

Originally Posted by RaphaelP;

In B2:

Formula:

Please Login or Register  to view this content.

In C2:

Formula:

Please Login or Register  to view this content.

Originally Posted by Arkadi

The tough part is none of us seem to be able to replicate many of your errors.... and the formats are incredibly inconsistent, some text, some date/time, and of the date/time ones, sometimes a leading 0 for single digit hours or months, while other times no leading 0's, I also see some dates are month/day and some day/month.

How is the data initially obtained? It may be easier to fix the source of the data collection... I think you mentioned csv, but how is that generated?

I got that sent to me by someone. There's too many variables on how the data got added there, lol.. so going through source would be out. Does it matter that I'm on the Mac OS version of Excel? Maybe that's why I'm getting these errors?

Originally Posted by Raphaelp

I was out yesterday... why don't you just select column A and press ctrl+H. Find what:, [comma] Replace with:[leave this blank], then click "Replace All".

Then do as I wrote above and see what happens:

Thank you sir... it still gave me an error
Here you go.

Screen Shot 2017-06-22 at 4.43.49 PM.png

Also attaching the file with progress thus far.

Here's the devil file

Guys, for real, I am astounded as to how much you guys are trying to help and I am super embarrassed that there's nothing much I am able to do other than copy/paste I apologize, but I am very thankful.

Originally Posted by JeteMc

Try the following formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Ugh...it gave me numbers

Jun_19_-_9am.png

Originally Posted by AlKey

I think what needs to done is after you ran the formula do copy and paste values only. This way excel will properly convert to the local regional settings.
This formula is for Dates

Formula:

Please Login or Register  to view this content.

and for Time

Formula:

Please Login or Register  to view this content.

It gave me that famous VALUE error! So frustrating!
But in other news, your Excel file that you gave me worked flawlessly and it came out so neatly!!! Everything on your sheet works! So weird!

Originally Posted by JeteMc

Here is another version of the formula I proposed earlier:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.
Edit: @ Arkadi, much appreciated.

Same error as the one I showed in the screenshot above man

Originally Posted by AlKey

I guess my idea worked! This was the only way to get it done. Formulas worked fine on our side but when the file was sent to you, you would get errors. So, after I ran formulas I copied new Dates and Time and used Paste Special values only. This would remove formulas and leave Dates and Time only.
Glad it worked out for you.

Super strange because I tried the same thing and it wouldn't work. If all the positions are the same, I'm going to just copy paste it in to the original sheet.

Originally Posted by JeteMc

As Arkadi states the numbers in cells B862 and down, as shown in the screenshot in post #34, are the number of days since Jan. 1st, 1900. To get those cells to display as dates press the Ctrl and 1 keys, and apply the same date formatting (yyyy/mm/dd;@) as is applied in the other cells in that column.
Let us know if you have any questions.

Thanks guys!
Here you go, I tried doing what you said ... and it still wouldn't work with tht formula

It's a video.

Originally Posted by JeteMc

The video show formatting being applied to A862.
The formatting needs to be applied to column B, especially B862 and down.
Here is a copy of the file from post #29 with the formula and formatting applied to column B.
Let us know if you have any questions.

This worked! OMG wow!
haha
This is amazing..

Thanks so much guys, really appreciate it.
PS: Sorry for the delay, I was under the weather, so wasn't checking this...

Done, thanks again!