Formatting text dates & times in Excel is a Bind!

[Patish]

Hi

I imported a text file into Excel. There are cells with dates and times that I need to seperate between the dates and the times and format them in a particular way. Open the file and you see my request. I just can't seem to format the cells. Some of them do format, some don't and some fall in the middle: It's a mess.

Can you help, please?

[JBeaucaire]

C5: =IF(B5="", "", MOD(B5,1))
...formatted as Time.

D5: =IF(B5="", "", INT(B5))
...formatted as Date.

Now copy those two cells downward.

[dilipandey]

Hi Patish,

Use below formula for Time:-
=TIME(HOUR(B5),MINUTE(B5),SECOND(B5))

and below formula for Date:-
=DATE(YEAR(B5),MONTH(B5),DAY(B5))

See attached: Closed Chks Room Service Aug 2012-01.xls


Regards,
DILIPandey

<click on below 'star' if this helps>

[mrice]

Have you tried formatting the cells in the Date/Time column as text during the import? This should stop Excel attempting to auto reformat.

You could then use the TIMEVALUE and DATEVALUE functions on substrings.

[Kevin UK]

Hi Patish

Would this be something that you could work with.

[Patish]

Kevin

You hit it rock solid. Thanks for the solution. A bit complicated, but effective. If you got time to explain it or is it inexplicable.

Thanks to the others. Much appreciated.

Thanks

Patish

[Kevin UK]

It took me a while to get my head around it as the length of characters in column B ranged between 8-18 characters.
For the dates I used a IF Function and for the logical test I used the ISTEXT to determine if it was text in Column B.If it was true I used the DATE Function, with Text functions to extract the date. IE: 8/18/2012. If it was FALSE I extracted the Integar.
For the Times it was the same process as getting the dates, for the false part it was removing the Integar.
Hopefully that explains it!

And formating the columns as Dates and Times.

[Patish]

Thanks and much appreciated.

Patish

[Cutter]

@ Patish

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

[Patish]

Cutter
My apologies. Thank you. I am satisfied.
Patish