Rounding milliseconds from Time (HH:MM:SS)

[JCTalk]

Hiya,

I'm using some conditional formatting to compare two time values from two different days but its not working correctly due to the time including milliseconds. I want to round the time to the nearest whole second (HH:MM:SS), not just the display format.

Does anyone have any thoughts on a short quick formula/function (non-VBA) that I can use to convert my HH:MM:SS.SSS to HH:MM:SS?

Thanks guys.

 

[Xiq]

Hi JCTalk,

Try the following formula

=INT(A1*86400)/86400

 

[JCTalk]

Hiya Xiq,

Apologies for the delay in replying, been away.

That formula was fantastic thank you. Seemed to do what I was looking for.

I note the function INT is to round down. Is there a function that will round down or up based on the standard rounding rules?

Many thanks

 

[NARAYANK991]

Hi ,

There does not seem to a comprehensive discussion of all the functions in Excel which deal with this topic. You can look at individual sites which discuss the following functions :

INT , TRUNC , FLOOR , CEILING , ROUND , ROUNDUP , ROUNDDOWN , MROUND

There may be more , but these should be enough to do almost anything.

Narayan

 

[Xiq]

Hi,

Here is the modified version that will round down or up based on the standard rounding rules.

=ROUND(A1*86400,0)/86400