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

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

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)

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?

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

``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1