calculating time accurately

[james7705]

please can someone help me.

i have a opened date[default value =Now()]
and
resolved date[default value =Now()].

i would like to calculate the time elapsed between the two dates.
it is normally on the same day, but there are instances where it can run past midnight.

 

[pbaldy]

Check out the DateDiff() function.

 

[james7705]

been looking all over and following links to suggestions and tips and i'm still not getting it right. i have created a query based on the table using only the time controls i.e "opened date" and "resolved date". in the field tab of the query i created a field called "Time Taken"

Time Taken: DateDiff("h";[Opened Date];[Resolved Date])

when i create a form based on the query the time taken field gives me a number

opened date 2014-10-02 13:31:17
resolved date 2014-10-14 15:25:41
time taken 290

what have i done wrong?

 

[fat controller]

The 290 is referring to the number of hours between the two dates & times.

The "h" in your DateDiff statement means that you want it to return a number of hours taken, so 12 days x 24hrs per day = 288hrs, then the other two hours come from it being started at 1331hrs on the 2nd, and ending at 1525hrs on the 14th

 

[pbaldy]

Nothing; that's the number of hours between the 2. I know you've been given further advise elsewhere to format that value, so I won't duplicate it here.

 

[burrina]

Syntax Error: Try this.

TimeTakenateDiff("d",[Opened Date],[Resolved Date])/60

On the form you may want to use hh:nn:ss as your format.

HTH

 

[james7705]

Ok. So now how do i format that value to show "dd, hh:nn" ? Or if days are zero then "hh:nn"?

 

[james7705]

Syntax Error: Try this.


On the form you may want to use hh:nn:ss as your format.

HTH

Ok. Cool. I'll try that

 

[burrina]

I'm assuming you are wanting to display this as you said on a form using a text box.
If so, format as above. See post 6.

 

[pbaldy]

I'll get out of the way, but good luck with your project!

 

[james7705]

I'll get out of the way, but good luck with your project!

Thanks pbaldy. This project has become a lit bigger than i anticipated. The basics are ok for me, but I'm struggling with the cide as i have almost no knowledge or experience with it.

 

[james7705]

I'm assuming you are wanting to display this as you said on a form using a text box.
If so, format as above. See post 6.

When i format as quote 6 above with a comma instead of a semi colon it gives me a error. Thats why i have it the way i do. It doesn't give me an error then.
so now all i do is format the text box with "dd, hh:nn"?
Will give this a try tomorrow and see how it goes.
Thanks for the advice and help.
I'll let you know how it goes

 

[JHB]

When i format as quote 6 above with a comma instead of a semi colon it gives me a error. ..

Only for info: If you've to use a comma or a semicolon depend of where you're in the would, ex. in Europa we use semicolon, in US they use comma!