How can I fix multiple dates & numbers in one field?

[stephanbrown]

My colleague recently left the company, and I inherited some of his duties. We are not in a technical position, but were the 'most' technical. Here is the current dilemma.

He used an infopath form in Sharepoint to track work on specific tasks. To simplify, the form would have:

Name
Description
Date
Duration
Task Code
Status

The problem is that a repeating table was used to add dates & durations to the same ticket.

When I export to Excel, the multiple dates & durations show up in the same cell (on 2 separate lines though) and of course I can't calculate anything with it. [To clarify the multiple dates are in one cell and the multiple durations are in another cell]

My predecessor was working on a solution and someone puch more technical used Python to bring it into SQL and says the data is replicated every morning and all I have to do is run a query.

However, when I did that, the dates & times are still within the same cell (though instead of 2 separate lines there is just a space between them) and I still can't get Excel to be able to do anything with this data.

Any suggestions?

Also, I'm able to connect to this SQL database through SMSS but not directly through Excel? I work remotely and nobody actually gave me much information, which is pretty frustrating.

[JBeaucaire]

If you post a workbook with an example set of data showing BEFORE (what you have) and AFTER (what it exactly needs to be converted to), I can probably give you a macro you can run to correct this data so you analyze it.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.