Calculating duration (1 Viewer)

[jpl458]

I have two fields in a row in the table. StartTimelcl and EndTimelcl, and I want to calculate the difference in minutes and seconds between the two fields. I have tried a few different approaches, none of which worked. Both times are date/time format; 1:52:01 PM and 1:54:37 PM, I would like the output tp be in mm:ss format. Using expression builder in query. Here is what i tried:

Duration: DateDiff("s",[PhoneCalltbl2]![StartTimeLc], [PhoneCalltbl2]![EndTimelLcl]/60

I also tried subtracting the start time from the end time. which didn't work. I get results that are way bigger than they should be.

Tried using format, but couldn't get it to work

Duration: Format(DateDiff([PhoneCalltbl2]![ETimeGMT],[PhoneCalltbl2]![StartTimeLc],"mm:ss"))

Thanks

 

[The_Doc_Man]

Duration: Format( EndTimeLcl - StartTimeLc, "mm:ss" )

IF both of those variables have a date and time in them, you can just subtract them and use the format in question. HOWEVER - if the time can exceed an hour, your problem isn't the difference - it is the formatting. Access doesn't have a format for minutes greater than 60. So if you have a 61-minute phone call, you either need hours in the format or you need to define your own formatting routine.

I also tried subtracting the start time from the end time. which didn't work. I get results that are way bigger than they should be.

My guess is that one or the other of those variables didn't contain a date, OR you subtracted in the wrong order.

EDIT: Look at the "Similar Threads" list at the bottom of this thread, as it has several links to other forum threads on time formatting.

 

[jpl458]

Found the problem, in the format I was using "mm:ss", when it should h ve been "nn:ss". Damn computers are so persnickety, it should have known what I wanted. Now, all is right with the world, till I try something else.

 

[GPGeorge]

Found the problem, in the format I was using "mm:ss", when it should h ve been "nn:ss". Damn computers are so persnickety, it should have known what I wanted. Now, all is right with the world, till I try something else.

One of my go-to sayings about how humans relate to computers is this:

Ambiguity is required for successful humor and poetry, and essential to politics.
Ambiguity is the enemy of databases.

"mm" <> "nn" and that's no joke.

 

[The_Doc_Man]

Sorry, I should have caught that error. I was apparently distracted.

 

[The_Doc_Man]

You can go to more reputable sites like XXXXXXX

Sandra, the reference to the site you named led me to a gaming site. That kind of link or reference is not allowed on this forum, I have removed the reference you made and redacted it in the quote-link.