Rounding DateDiff Formula

[Tophan]

Good evening,

Using the DateDiff formula I have been calculating the total time worked. I need to round this formula to the nearest quarter of an hour.

How can I amend my formula =DateDiff("n",[StartTime],[EndTime])/60 to achieve this?

Thanks in advance for your response.

 

[plog]

Right now your DateDiff gets minutes:

DateDiffMinutes

Then you convert it to hours by dividing by 60:

DateDiffMinutes / 60

For a quarter hour...quarter it:

(DateDiffMinutes / 60) / 4


Bonus--use math to reduce your fractions.

 

[The_Doc_Man]

Watch the parentheses on that. Datediff returns a Variant, true, but it will try to return a LONG in that variant.

DateDiff Function - Microsoft Support

Use the DateDiff function in Access to returns a Variant (Long) specifying the number of time intervals between two specified dates.

support.microsoft.com

If you use (DateDiffMinutes / 60) / 4 then you might get something fractional (due to VBA's automatic expression up-typing). I believe from what I read that Tophan wants a count of 15-minute intervals, I.e. quarters of an hour. Try DateDiffMinutes\15 because you have minutes and you want 15-minute intervals. This will give you the truncated number of 15-minute intervals. For those who weren't sure, the \ operator is integer divide whereas the / operator is the type of divide that will allow fractions.

 

[CJ_London]

To round to the nearest 15 minutes add 7.5

?(37+7.5)\15
2
?(38+7.5)\15
3

 

[arnelgp]

you can create a udf:

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' Factor is the optional multiple to which you want to round, defaulting to 1
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function

change your calculation to:

Ceiling(DateDiff("n",[StartTime],[EndTime]), 15)/60

 

[Tophan]

I am having trouble understanding the suggestions.

If with my original formula, an individual worked 7.55 hours, the person keying the information needs to see that they worked 7 hours 35 minutes, rounded to 7 hours 30 minutes (7.30). So it will always be total hours and minutes worked, rounded to the nearest quarter of an hour.

I've tried some of the suggestions above but can't get the formulas to work. So for DateDiffMinutes I typed =DateDiffMinutes([StartTime],[EndTime])/15 also tried \15 but it's not working.

I also tried the Ceiling(DateDiff... formula but that is returning #Name?

 

[CJ_London]

but it's not working.

what exactly does that mean? you get an error? wrong result? if so what is the error? what is the data and what is the you get and what do you expect? what is the formula you are using that does not work?

 

[Tophan]

Hi,

So these are some of the variations of the formulas I have tried...I think I am typing it incorrectly because every formula except 1 is returning the #Name?

=DateDiffMinutes("n",[StartTime],[EndTime]/60)/4 = #Name?

=DateDiffMinutes([StartTime],[EndTime]/60)/4 = #Name?

=DateDiffMinutes([StartTime],[EndTime]/60)/15 = #Name?

=Ceiling(DateDiff(“n”,[StartTime],[EndTime]),15)/60 = #Name?

=DateDiffMinutes([StartTime],[EndTime])\15 = #Name?

=DateDiff("n",[StartTime],[EndTime]+7.5)/15 = 750.33

Your help is really appreciated!

 

[Tophan]

I just received an email from the owner of the database who advised they gave me wrong information which I, unfortunately, passed on to this forum. Please accept my sincerest apologies.

The original formula =DateDiff("n",[StartTime],[EndTime])/60 is returning figures like 8.17 or 6.80. They would like this number rounded to the nearest quarter so 8.17 would round to 8.15 and 6.80 would round to 6.75.

I had incorrectly stated earlier that it should round to 15-,30-, or 45-minutes when it should be .25, .50 or .75 or the nearest whole number.

Again, my apologies for this misinformation and I hope you can still help me with this problem.

Thanks

 

[theDBguy]

I just received an email from the owner of the database who advised they gave me wrong information which I, unfortunately, passed on to this forum. Please accept my sincerest apologies.

The original formula =DateDiff("n",[StartTime],[EndTime])/60 is returning figures like 8.17 or 6.80. They would like this number rounded to the nearest quarter so 8.17 would round to 8.15 and 6.80 would round to 6.75.

I had incorrectly stated earlier that it should round to 15-,30-, or 45-minutes when it should be .25, .50 or .75 or the nearest whole number.

Again, my apologies for this misinformation and I hope you can still help me with this problem.

Thanks

Hi. Did you mean to say round 8.17 to 8.25 instead of 8.15?

 

[Tophan]

Hi. Did you mean to say round 8.17 to 8.25 instead of 8.15?

Yes...I'm confusing myself now!

 

[plog]

To do that you would use your original expression and use the Format function()

MS Access: Format Function (with Numbers)

This MSAccess tutorial explains how to use the Access Format function (as it applies to numeric values) with syntax and examples. The Microsoft Access Format function takes a numeric expression and returns it as a formatted string.

www.techonthenet.com

 

[Tophan]

Thank you

 

[CJ_London]

don't know if you know this but date/time is stored as a decimal number. The bit before the decimal point represents the number of days since 31/12/1899. The bit after the decimal point is the number of seconds/86400 (the number of seconds in a day).

i.e.
?cdbl(now())
44065.6367013889

So you need to be clear what you actually want the number of hours expressed as a fraction - i.e. 7.00, 7.25, 7.5, 7.75, or a time 7:00, 7:15, 7:30, 7:45.

This will give you the former
=((DateDiff(“n”,[StartTime],[EndTime])+7.5)\15)/4

This will give you the latter
=format((( DateDiff(“n”,[StartTime],[EndTime])+7.5)\15)*15*60/86400,"hh:mm")

assuming datediff gives you 160 minutes which is 2.66666666666667 hours
?((160+7.5)\15)/4
2.75
?format((( 160+7.5)\15)*15*60/86400,"hh:mm")
02:45

incidentally - if start and end times are just the time element and the start time is yesterday and end time today, your formula will be incorrect. Both times also need to include the day

 

[Tophan]

Thank you. The first formula - =((DateDiff(“n”,[StartTime],[EndTime])+7.5)\15)/4 - works best for me.


Thank you so much for your help!