# Round to nearest 15 then nearest hour if less than 1 hour

1. ## Round to nearest 15 then nearest hour if less than 1 hour

I have a form to round to nearest quarter but if it is less than 1 hour I need it to round to a total of 1
can this be combined in one formula.

I also need my time to be configured so that if the start time is a PM number then end time AM it does not figure right. is there a way to remove the AM/PM from time.
I have already tried all the formats from number,time, & custom.

Attached is my formTimesheet Form 2014.xlsx  Register To Reply

2. ## Re: Round to nearest 15 then nearest hour if less than 1 hour

Hi BLOUNTFIRE

Welcome to the forum. If you put the following formula in column M (format as hh:mm), it will do as you ask ``Please Login or Register  to view this content.``
To explain:
1 - the "(L18<I18)*1" means add 1 (day) if the end time is less than the start time ( (L18<I18) is Boolean, so either 1 if true or 0, so 1*1 or 0*1)
2 - Max(xxx,yyy) (self explanatory) 1 = day hence 1/24 = hour, 1/24 = .041667

Let me know if you have any questions

Regards
Alastair  Register To Reply

3. ## Re: Round to nearest 15 then nearest hour if less than 1 hour

Doesn't the result need to be a decimal number of hours? Try this to cope with crossing midnight, to return decimal hours, with a minimum of 1

=MAX(1,MOD(L18-J18,1)*24)  Register To Reply

4. ## Re: Round to nearest 15 then nearest hour if less than 1 hour

=MAX(1,MOD(K24-I24,1)*24)

using this formula which works great. Thank You.
Now I have to have the total to show up at the bottom. It only shows #VALUE! How do I make #VALUE! show as a 0?  Register To Reply

5. ## Re: Round to nearest 15 then nearest hour if less than 1 hour ``Please Login or Register  to view this content.``  Register To Reply