Round Seconds Up To Nearest Minute

[abbaddon223]

Hello all,

I'm hoping someone out there can be very kind and help me please!!

I have a field [Duration] which contains a numeric value which relates to seconds that a call has lasted for. So, typical values can be

0
2
157

Any value really, but they are all seconds.

I have another field in the same tables [Duration_Minutes]. What I would like to do is take the value of [Duration] and round it UP to the nearest minute, so in the examples above:

0 (seconds) would still be 0 (minutes)
2 (seconds) - would become 1 (minute)
157 (seconds) would become 3 (minutes)

etc etc etc. The simple calculation in my simple brain is obviously, seconds divided by 60 to get the minutes, but I don't know how to round it UP (always UP, no matter how close it is to the lower minute).

Any help greatly appreciated :banghead:

 

[BigHappyDaddy]

I am assuming you want a query to accomplish this task. If not let me know:

UPDATE Table1 SET Table1.Duration_Minutes = IIf(Int([Duration]/60)=[Duration]/60,[Duration]/60,Int([Duration]/60)+1);

 

[abbaddon223]

What a total legend!! Thank you so much - really did the trick!!!

 

[boblarson]

Just for both of your knowledge you can do it also like this (shorter):

IIf([SecondsField] MOD 60 > 0, [SecondsField] \ 60 +1, [SecondsField] \ 60)

 

[BigHappyDaddy]

Just for both of your knowledge you can do it also like this (shorter):

IIf([SecondsField] MOD 60 > 0, [SecondsField] \ 60 +1, [SecondsField] \ 60)

And that is why Bob Larson is the MASTER.

 

[nanscombe]

How about

([Duration]\60)-([Duration] Mod 60>0)

([Duration]\60) - Giving you whole minutes
([Duration] Mod 60>0) Returning 0 for no seconds and -1 if there any seconds at all.

x - -1 = x + 1

 

[BigHappyDaddy]

([Duration]\60) - Giving you whole minutes

Won't that still give you any fractional part of the minute (if it exists)?

 

[nanscombe]

([Duration]/60) - Gives the remainder i.e. 66 / 60 = 1.1

([Duration]\60) - Rounds the number down i.e. 66 \ 60 = 1

 

[BigHappyDaddy]

Whoops - I missed that!

I think if I were to use that little trick, I would have to add an extra comment somewhere explaining the '/' versus '\'. I have got to think it would trip someone up who was reviewing code and / or queries at a later date.

Thanks for the refresher.

 

[nanscombe]

Arithmetic operators

/ Divide the first number by the second number. [Total]/[ItemCount]

\ Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer. [Registered]\[Rooms]

If you notice, boblarson ("MASTER") used the '\' instead of '/'.