Formula to round up time to the nearest given end time.

[Shellybelly]

Does anyone know a formula that will round up time to the nearest end time?

Example:

An employee's end time is 4:45 pm but the employee finished at 4:43 pm. How can i get the 4:43 pm round up to the 4:45 pm end time?

another example is:

An employee's end time is 4:45 pm but the employee finished at 4:50 pm. How can i get the 4:50 pm round up to the 4:45 pm end time?

Can anyone help me with this?

I've wrote the formula:

Please Login or Register  to view this content.

But what the formula does is that it rounds up but is off by a couple seconds or sometimes minutes.

If there is a formula that can give the exact end time I will be sooo happy.

[Special-K]

Will the end time always be 4:45 ?

[aimone111]

Hi

The way I have previously done this is by rounding to the nearest 5 minutes, in the next column enter the below. (Sorry forgot to mention, of course the 5 minutes can be changed to 10 or 15 minutes.)

=(ROUND((I11*1440)/5,0)*5)/1440
=(ROUNDUP((I11*1440)/5,0)*5)/1440

Thanks

[MarvinP]

Hi Shelly,

This sounds like a job for MRound(). See instructions and examples at:

http://www.techrepublic.com/blog/win...l-time-values/

[Tony Valko]

Does anyone know a formula that will round up time to the nearest end time?

An employee's end time is 4:45 pm but the employee finished at 4:50 pm. How can i get the 4:50 pm round up to the 4:45 pm end time?

4:50 to 4:45 would be rounding down.

[Shellybelly]

Thanks to all of you the formulas worked flawless. lol