Rounding Time

[Guest]

I would like to round the time to the nearest quarter hour. Example: round
01:08 PM to 01:15 PM, round 01:07 PM to 01:00 PM.

 

[RuralGuy]

I would like to round the time to the nearest quarter hour. Example:
round 01:08 PM to 01:15 PM, round 01:07 PM to 01:00 PM.

Answered yesterday by MVP Steve Schapel in the Forms forum
_____________________________________________
Caution: air code (untested)...

Dim InputTime As Date
Dim CurrentSeconds As Integer
Dim RoundedSeconds As Integer
InputTime = Now
CurrentSeconds = Minute(InputTime) * 60 + Second(InputTime)
RoundedSeconds = CurrentSeconds Mod 900
If RoundedSeconds < 450 Then
Me![In] = DateAdd("s", RoundedSeconds * -1, InputTime)
Else
Me![In] = DateAdd("s", 900 - RoundedSeconds, InputTime)
End If

 

[Allen Browne]

Try something like this, where "dt" represents the name of your date/time
field:

= DateAdd("n", 15 * CLng(DateDiff("n", #00:00:00#, TimeValue(dt)) / 15),
DateValue(dt))

Explanation:
1. TimeValue() parses the time part of the date.
2. DateDiff() gives the number of minutes since midnight.
3. Dividing by 15 gives the number of quarter hours.
4. CLng() rounds that to a whole number of quarter hours.
5. Multiplying by 15 turns that back into minutes.
6. DateAdd() adds that number back onto the DateValue() of the original
date/time value.

 

[Guest]

Thanx a lot RuralGuy

I would like to round the time to the nearest quarter hour. Example:
round 01:08 PM to 01:15 PM, round 01:07 PM to 01:00 PM.

Answered yesterday by MVP Steve Schapel in the Forms forum
_____________________________________________
Caution: air code (untested)...

Dim InputTime As Date
Dim CurrentSeconds As Integer
Dim RoundedSeconds As Integer
InputTime = Now
CurrentSeconds = Minute(InputTime) * 60 + Second(InputTime)
RoundedSeconds = CurrentSeconds Mod 900
If RoundedSeconds < 450 Then
Me![In] = DateAdd("s", RoundedSeconds * -1, InputTime)
Else
Me![In] = DateAdd("s", 900 - RoundedSeconds, InputTime)
End If

 

[Guest]

Thanx again Allan!

Try something like this, where "dt" represents the name of your date/time
field:

= DateAdd("n", 15 * CLng(DateDiff("n", #00:00:00#, TimeValue(dt)) / 15),
DateValue(dt))

Explanation:
1. TimeValue() parses the time part of the date.
2. DateDiff() gives the number of minutes since midnight.
3. Dividing by 15 gives the number of quarter hours.
4. CLng() rounds that to a whole number of quarter hours.
5. Multiplying by 15 turns that back into minutes.
6. DateAdd() adds that number back onto the DateValue() of the original
date/time value.

 

[RuralGuy]

Thanx a lot RuralGuy

You're very welcome. Glad I could help.