Sorry if this comes across as unclear, it's sort of a fuzzy problem to begin with. And requires far to much explanation.
I am getting information from a source which I have no control over. From the source there is a start time for an event, and a duration for the event. I need to combine the two to create an end time, or a stop time for the event. This wouldn't be so hard for me if it weren't for the ridiculous way in which the information is formatted at the source. There is a field for Date, Time, and Duration and they go like this:
Date: Date is text displayed as 1110201, and would be February 1, 2011. 1100903, would be September 3 2010. The first 1 = the first two numbers of the year, in this case 20, the second two numbers equal the last two numbers 10, the middle two are the month and the las two are the day 110 = 2010, 111 = 2011, 02 = February and 01 = the first. I need this to display in short date, 2/1/2011.
Time: Time has no colons. 11:35:22 comes across as a text field. The time is military. So 233521 is 11:35:21 PM (HH:MM:SS), I need the query to display it as that but that leads me to the second problem.
Duration: Is just a single number in MINUTES. Durations can be for an entire date and would display as 1440, or multiple days, or 1560. This would be 1,560 Minutes. No seconds, no hours, just minutes.
So the problem is I need to get ALL of this information to come out of a query, not display in the manner it's displayed in and chomp it up in excel, which I could do for myself, but it's not for myself and the people utilizing it need it to come out as Date Start Time Stop Time. Where the stop time is calculated between the Duration and Start Time...
Could anybody give me any assistance? Or am I completely out of luck here, heh.