# Calculating a time string not formatted as Date/Time

1. Novice
Windows XP Access 2010 64bit
Join Date
Feb 2011
Posts
8

## Calculating a time string not formatted as Date/Time

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.

2. Court Jester
Windows XP Access 2003
Join Date
Feb 2010
Posts
1,368
Will you be using these as criteria in queries or do you want it formatted for display purposes? If the former, It's gonna take some fun VBA. if the latter, not too hard.

3. Novice
Windows XP Access 2010 64bit
Join Date
Feb 2011
Posts
8
Oh I think we all know the answer to that! The events are setup linked to a SQL farm that they can only access via Access. The queries are for other people not for myself. I usually crunch the information in Analyzer and put the reports elsewhere for viewing Crystal/PDFs.

But ya, criteria for queries....several queries. A single person's full day of events, search for every event that goes over midnight, search for all events whose end time is past 5:00 PM, Show all people and event type that is longer than 24 hours...so on and so forth. It's a whole bunch of queries that are going to be run against these things by a whole group of people.

The linked table is updated in real time, and this is for real time coaching. It would be so great, or so easy if it were for a report I could just put out in Excel for the previous day, but it's just not the case.

4. Court Jester
Windows XP Access 2003
Join Date
Feb 2010
Posts
1,368
Actually, I take back the VBA part. This could be done a bit easier. the first three numbers in your date, 111, would be 2011, 110, 2010. How are years in the 19xx's displayed? Are they even needed?

5. Novice
Windows XP Access 2010 64bit
Join Date
Feb 2011
Posts
8
There aren't any. This specific datasource does not have anything before 2009, 109.

6. Court Jester
Windows XP Access 2003
Join Date
Feb 2010
Posts
1,368
Here's the date part. I will update this post with the rest.
date:
CDate(mid([fieldName],4,2) & "/" & right([fieldName],2) & "/20" & mid([fieldName],2,2))

time:
CDate(left([fieldName],2) & ":" & mid([fieldName],3,2) & ":" & mid([fieldName],5,2))

duration:
concatenate [startDate] and [startTime] to form a "long date" datatype. Once you do, update your [endTime] with

7. Novice
Windows XP Access 2010 64bit
Join Date
Feb 2011
Posts
8
Time + Duration = End Time

8. Court Jester
Windows XP Access 2003
Join Date
Feb 2010
Posts
1,368
The first two i tested and can confirm the syntax is good. The third is more of a logical thing you can work out the syntax to.

Moral of the story, the major text-to-date functions are CDate() and Format(). The addition to or subtraction from date functions are DateAdd(), DatePart(), DateDiff(), etc

9. Novice
Windows XP Access 2010 64bit
Join Date
Feb 2011
Posts
8
Thank you. The help is much appreciated. Both the first two work magnificently

10. Court Jester
Windows XP Access 2003
Join Date
Feb 2010
Posts
1,368
Anytime. Once you get the third one working, go ahead and mark the thread as solved (from the "thread tools" menu) so others can search through the forums for it.

11. Novice
Windows XP Access 2010 64bit
Join Date
Feb 2011
Posts
8
Will do, I havent had time to try and concatenate the date and time to make a date/time longdate field yet. If my duration is in minutes should I multiply it by 60 and then do the DateAdd?

12. Court Jester
Windows XP Access 2003
Join Date
Feb 2010
Posts
1,368
No. the "n" in DateAdd tells the function that the number being added is in minutes. It should be good as-is.

13. Novice
Windows XP Access 2010 64bit
Join Date
Feb 2011
Posts
8
Petty cool. Work liked a charm, easier than I had anticipated. Thanks again.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums