Excel not accepting values as "time" from Access

[Jaffey]

I have an Access query that passes phone switch data to an Excel Pivot Table. I use the following expression to parse the date

Time: Mid([CallDate],13,5)

which gives me normal looking time values like
14:52
11:33
18:21
etc.

This is fine for display purposes but Excel is not recognizing them as time values so I can not perform any functions with them. I would like to use MIN and MAX so that I can display each agent's first and last calls of the day. Is there a way to have Access pass the values in a way that Excel will recognize them as time values?

 

[AnotherHiggins]

The MID function returns a string - not a number.

In Access, try this instead:
Time: TimeValue(Mid([CallDate],13,5))

It might not look like a time in Excel. That's OK. Just format the column in Excel as time.

NOTE: If the field [CallDate] contains both date and time data, [highlight]DO NOT BREAK IT UP[/highlight]! Instead, just change the format of the [Time] field.

For a better understanding of how and why you are having these problems, see faq68-5827

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.