datediff function

[osi2301]

I need to return the difference between two different times and dates in both hours and minutes. I am using the datediff function to calculate this in hours. The only problem is the datediff function returns a result that is rounded to the next whole hour. I need to be more specific than this. That is, I need to have a result that returns hours and minutes or ,at the very least, the hours shown as a fraction. Any ideas?

 

[earthandfire]

Why don't you get the result in minutes and then divide it up into the units you require?


Hope this helps.

 

[osi2301]

I've done that but I using a continous form and in those cases where a person has a day off (this is a rostering database) the datediff returns an error when there is no sign on time. If I use the datdiff function in "minutes" instead of hours and divide by 60 I get the resuts I need. But there is that error message when there is no sign on.

 

[earthandfire]

You could wrap the sign on and / or sign off times in Nz or IIf and return a 0 when either is null.

Hope this helps.

 

[osi2301]

I;m already using the IIF function in this: =IIf(IsDate([off1]) And IsDate([on2]),DateDiff("h",CDate([off1]),CDate([on2])),"")

The above returns the difference in hours but rounded to the next highest hourly increment. I've tried changing the above to return the minutes instead of the hours like this:=IIf(IsDate([off1]) And IsDate([on2]),DateDiff("n",CDate([off1]),CDate([on2])),"")/60

This returns the hours and minutes but during the time the next sign on time is blank it returns #ERROR, until the next roster (sign on time) is selected, then it changes to the time in hours and minutes.

 

[earthandfire]

IIf very kindly evaluates all in its entirety. That is to say, whether or not the condition fails, both the true and false parts are evaluated.

Try

=IIf(IsDate([off1]) And IsDate([on2]),DateDiff("n",CDate(IIf(IsDate([off1]),[off1],0)),CDate(IIf(IsDate([on2]),[on2],0)))/60, "")


I've just copied, pasted and edited your post, so check the brackets etc.

Hope this helps.

 

[osi2301]

wow...That's great and it does the trick. Thanks

 

[earthandfire]

Glad it works, but there has to be a neater solution - surely