need to calculate the call duration

[Randy]

I have a table, from a Cisco call application. It gives me the call start date/time and call End date/time. However as of right now it gives it to me in text format. :banghead:

I have no idea why a data base would store date time as text but it does. Anyway long term I am going to contact cisco and see if there is an answer. Short term I need a formula.

Fieldnames
enddatetime
startdatetime
so both fields are consistent where "T" separates the date and time

YYYY-MM-DDTHH:MM:SS

startdatetime 2014-12-04T13:55:01
enddatetime 2014-12-04T14:03:30

or
startdatetime 2014-12-04T23:55:01
enddatetime 2014-12-05T00:03:30



I was looking at something like this
callduration: IIF(StrComp(Left([contactcalldetail]![enddatetime],10),Left([contactcalldetail]![startdatetime],10)=0,Right([contactcalldetail]![enddatetime],8)-Right([contactcalldetail]![startdatetime],8),not sure how to calculate when it rolls over to new day)

some how the second part of the IIF statement has to add 1440 minutes to the end time??

Is there a better way. Still searching Google, but was hoping this was a common issue for other companies that have call centers.

 

[Randy]

ok I did figure out how to do it when the call lasts at least a minute
callduration: DateDiff("n",Left([contactcalldetail]![startdatetime],10) & " " & Right([contactcalldetail]![startdatetime],8),Left([contactcalldetail]![enddatetime],10) & " " & Right([contactcalldetail]![enddatetime],8))

However when a call lasts less than 60 seconds, i.e. 1 - 59 seconds, it returns 0

 

[jdraw]

See this for more info. Try "s" for seconds. You can always convert to minutes/hours etc.