Time rounding help

[jcaptchaos2]

Hello,


I am using this formula to round time

=ROUND(E7*96,0)/96

and am wondering if it could be modified to ignor the seconds as in cell "E7" I sometimes would have 3:37:35 and it will round forward to 3:45. I would like 3:37 to round back and 3:38 to round forward.

 

[T. Valko]

Hello,


I am using this formula to round time:

=ROUND(E7*96,0)/96

and am wondering if it could be modified to ignor the seconds as in cell "E7" I sometimes would have 3:37:35 and it will round forward to 3:45. I would like 3:37 to round back and 3:38 to round forward.

So what are you saying?

You would like 3:37:35 rounded to the nearest minute then rounded to the nearest 15 mins?

 

[jcaptchaos2]

I guess what I would like is if it was 3:37:59 to be rounded back to 3:30, it is a long story but what the employee sees is 3:37 and some see it get rounded to 3:30 and some see it get rounded to 3:45. I would like all of the 3:37's to get rounded back to 3:30.

 

[barry houdini]

try this version

=ROUND(E6*96-"0:48",0)/96

 

[T. Valko]

I guess what I would like is if it was 3:37:59 to be rounded back to 3:30, it is a long story but what the employee sees is 3:37 and some see it get rounded to 3:30 and some see it get rounded to 3:45. I would like all of the 3:37's to get rounded back to 3:30.

Still not sure what you want.

This will round to the nearest minute then round to the nearest 15 mins.

=ROUND((ROUND(E7*1440,0)/1440)*96,0)/96

 

[jcaptchaos2]

Thanks for the help, Barry your advice works perfect, T.Valko thanks for your advice also.

 

[T. Valko]

Thanks for the help, Barry your advice works perfect, T.Valko thanks for your advice also.

Glad you got a working solution. Thanks for the feedback!

 

[Scott Huish]

ROUND(E6*96-"0:48",0)/96

What is the significance of subtracting "0:48" in this formula?

 

[barry houdini]

I think you can achieve the required rounding by subtracting 30 seconds from the original value, i.e.

=ROUND((E6-"0:00:30")*96,0)/96

I just "simplified" that by multiplying the 30 seconds by 96 to get "0:48"....