Rounding time problems formula

[gjgj]

Hi,

I want to round different time entries but I'm having problems with the seconds.
If a time is less than 1 min, I want to round up to 1 min.
But if the time is more than one min, I want to round up or down depending on the seconds (if <29sec then round down to lower min, if more than 30 then round up).
EG
00:01:10 = 00:01:00
00:01:30 = 00:02:00
00:00:30 = 00:01:00
00:00:29 = 00:01:00

I have this formula below which works for most but it rounds down to 0 when the time is less than 30s. I tried several IF combinations etc but I can't get anything to work, any easy fixes out there I can add to the below (or something totally new)?

=HOUR(L260)*60+MINUTE(L260) + IF(SECOND(L260)>=30,1,0

Thanks!

 

[FormR]

Hi, does this fit the specification?

=MAX("00:01:00",MROUND(L260,"00:01:00"))

 

[gjgj]

Hi, does this fit the specification?

=MAX("00:01:00",MROUND(L260,"00:01:00"))

Thanks! but all results returned are 0 now...

 

[FormR]

all results returned are 0 now...

Hi, I don't think it's possible for that formula to return a zero - make sure you have formatted the cells that contain the formula as time.

Excel 2013
	L	M
260	00:01:10	00:01:00
261	00:01:30	00:02:00
262	00:00:30	00:01:00
263	00:00:29	00:01:00
Sheet4

Cell Formulas
Range	Formula
M260	=MAX("00:01:00",MROUND(L260,"00:01:00"))

 

[gjgj]

Sorry, they were formatted as Number (was using it outside existing table) - working now, thanks FormR!