How to round up/down time formats (hh:mm:ss) ???
I need to round up/down my "Duration" times into the next column, "Rounded Up/Down". So for instance, one duration call will state 00:01:37, I want that to say 00:02:00 in the next column labeled as Rounded Up/Down. Bottomline, any call over 31 seconds will be rounded up and any calls under 30 seconds will be rounded down. Can I insert a formula that will do this for the whole "Rounded Up/Down" column ? Thanks for your help guys !
Last edited by ek4jay; 10-11-2012 at 04:20 PM. Reason: typo
In D2 and copy down, =MROUND(C2, "0:01")
Entia non sunt multiplicanda sine necessitate
Thanks shg ! Worked !
Actually it didnt work. All my calls that are 30 seconds should be rounded down. Any calls over 31 seconds should be rounded up. Reason for that is because my company doesnt charge our customers for any call duration under 30 seconds.
So if the duration of the call is over 30 seconds, always round up to the next minute? Otherwise, round down (to 0) Is that what you are asking for?
Try this
=IF(C2<"0:00:30"+0,MROUND(C2,"0:01:00"),MROUND(C2+"0:00:30","0:01:00"))
Is that what you are looking for?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
For an example..... If the duration call is 1:31, it should be rounded to 2:00. Now if the call duration is 1:30, it should be rounded down to 1:00.Originally Posted by ChemistB
=mround(c2-"0:00:01","0:01:00")
Worked perfectly ! Thanks ChemistB !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks