This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi everyone,
I'm looking for a way to round time codes in excel. I have a list of time codes in hh:mm:ss:ff format where every 24 frames = 1 second.
For example, 00:00:24:12 would = 24 and 00:00:24:13 = 25. Just like 00:00:08:02 would = 8 or 00:00:10:23 would = 11
I have about 50 times codes that I need to round to the nearest second and as new codes come in it gets very time consuming.
I'm absolutely not proficient in excel so if anybody knows of a formula I could use that can round frame rates to the nearest second, that would be an absolute lifesaver.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Apparently, you are pursuing an alternative solution without informing me. See https://answers.microsoft.com/en-us/msoffice/forum/all/rounding-decimal-places-in-excel/baad4328-061a-41cf-bcc4-a468bdf7c091 .
So it is not worth my time and (considerable) effort to continue this discussion.
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
Hi Joe,
Basically we're working off 24 frames per second. So 12 frames and under would round down to the nearest second and 13 frames and over would round up. This works all the up to 00:00:59:(13-23) which would then become 00:01:00:00. However we don't really have durations that long.
So in a situation like 00:00:25:12, it would become 25, likewise, if it was 00:00:25:13, it would round up to 26.
I'm sorry, but I'm very slow at this and am unsure of how to input in the IF(A2="","",...) part.
If it's not too much trouble to explain the structure of the formula, I might be able to understand better so I don't become too annoying.
Unfortunately, you do not have an example like 00:00:25:12. Is that rounded up to 26 ("round half to even", since 00:00:24:12 is rounded down to 24), or is it rounded down to 25 (always "round half down")?
If we can assume that your data is always 11 characters (as your examples are), enter the following into B2 and copy down:
=IF(A2="", "", ROUND(TIMEVALUE(LEFT(A2,8))*86400, 0) + (RIGHT(A2,2)>"12"))
if we always "round half down".
For "round half to even":
=IF(A2="", "", ROUND(TIMEVALUE(LEFT(A2,8))*86400, 0) + (RIGHT(A2,2)>"12") + AND(ISODD(MID(A2,7,2)), RIGHT(A2,2)="12"))
[EDIT: I added IF(A2="","",...) so that you can fill column B with conversion formulas, and allow for more or less data "as new codes come in". Fill enough rows to accommodate the most amount of data you can reasonably expect. I presume it is less than 1+ million rows. (wink)]