DateDiff Function

[mattster1010]

Good Afternoon All,

I have used the above function to calculate the difference in hours between two dates. the formula is per below:

Expr1: DateDiff("h",[First Time],[Last Time])

How would I calculate the difference in hours & minutes? I have tried the following but get #error when returning the query results:

Expr1: DateDiff("hn",[First Time],[Last Time])

Any help would be appriecated.

Regards,

Mattster

[CreganTur]

Can you provide sample data that you would use for your [First Time] & [Last Time] fields?

I've got a theory, but I need to see the exact format of data you're using before I can say anything difinitive.

[mattster1010]

Hi CreganTur,

Here is the data sample as requested,

09:13:00

The format of the field is set to date/time.

If you need any more info let me know.

Cheers,

Mattster

[OTWarrior]

Use this as a public function, and plug in your number (as minutes):

Public Function strTime(n As Long) As String
strTime = IIf(n < 0, "-", "") & Format(Int(Abs(n) / 60), "00") & Format((Abs(n)) Mod 60, "\:00")
End Function
so you would use this in your query:

Expr1: strTime(DateDiff("n",[First Time],[Last Time]))

[mattster1010]

Hi OT,

I have created a new module called strTime and copied and pasted the code you gave. I have also amended my query to this below:


Expr1: strTime(DateDiff("n",[First Time],[Last Time]))

When I execute the query it gives the following error message:

Underfined function 'strTime' in expression.

I guess that means it can not reference the query strTime?

[OTWarrior]

I have just created a new module with a different name (but changing the function to the same name) and had no problems...

Hate to do this the long way Matt, but does this work??

Expr1: IIf((strTime(DateDiff("n",[First Time],[Last Time]))) < 0, "-", "") & Format(Int(Abs((strTime(DateDiff("n",[First Time],[Last Time])))) / 60), "00") & Format((Abs((strTime(DateDiff("n",[First Time],[Last Time]))))) Mod 60, "\:00")
(put this into your query as one field, this should do the same thing, but without calling the function. Note I have not tested this in this format)

PS: If Fumei looks at this, I am sorry for making it very long across the screen, but I feel this way it should be easier to copy.

[mattster1010]

Hi OT,

I have pasted the query example above and still get the following error message, is it because the query contains strTime?

Underfined function 'strTime' in expression

Cheers,

Matt

[OTWarrior]

sorry, sily mistake i made there (i have no idea why i put strTime in that query):

try this:

Expr1: IIf(DateDiff("n",[First Time],[Last Time]) < 0, "-", "") & Format(Int(Abs(DateDiff("n",[First Time],[Last Time])) / 60), "00") & Format((Abs(DateDiff("n",[First Time],[Last Time]))) Mod 60, "\:00")

[jigar1276]

Try this to get minutes

DateDiff("m",[First Time],[Last Time] mod 60)

you can write combine to get the result as follow:
DateDiff("h",[First Time],[Last Time]) & ":" & DateDiff("m",[First Time],[Last Time] mod 60)

[jigar1276]

Sorry about last reply. Please try this

datediff("n", [Firt Time], [Last Time]) mod 60

you can write the combination to get the desired result:

DateDiff("h",[First Time],[Last Time]) & ":" & DateDiff("n", [Firt Time], [Last Time]) mod 60

[mattster1010]

Thanks jigar1276 & OT both suggestions worked.

However I went for OT suggestion as it give the exact result I was looking for in hours and minutes.

I do have another question though, I have the total hours and minutes populated now in the following format:

03:10 (Column named TotalWorkingMinutes)

I want to divide the above column by another column called CountOfUpdated_By, the data for this column is basically a count of records in the table, data format is 29 for example.

So the SUM would be 29/03:10 or [CountOfUpdated_By]/[TotalWorkingMinutes]....but I seem to get #Error everytime I do this calculation? Is this because the data types are not the same, how would I fix this to get the result?

Thanks for help,

Mattster

[OTWarrior]

Excellent, glad we got it sorted for you :)

To answer your question, you aren't easily able to use this value for calculation, as you'd have to remove teh formatting and convert from a string back into a numeric value.

You'd be best off doing your calculation based on the same value from the start, as the initial data should be modified. IE:

Expr2: (DateDiff("n",[First Time],[Last Time]))/60