Solved Nested IF statements in Excel 2013 not working with hous cal

October 8, 2017 at 10:47:21
Specs: Windows 7
I need a result of no more that a 6. So 7 hours 29 minutes should return a 6 and 7 hours and 30 minutes returns a 6. However, a 5 hours and 29 minutes should return 5 and 5 hours and 30 miutes or more returns a 6. So 0-29 minutes rounds down and 30-60 rounds up to the next hour. Im using Excel 2013.

See More: Nested IF statements in Excel 2013 not working with hous cal

Reply ↓  Report •

#1
October 8, 2017 at 12:43:01
With your target Time in cell A1, try this:

=IF(MINUTE(A1)>=29,TIME(HOUR(A1),CEILING(MINUTE(A1),30),0),TIME(HOUR(A1),FLOOR(MINUTE(A1),30),0))

See how that works.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
October 10, 2017 at 10:08:20
✔ Best Answer
I believe that this will meet your requirements:

=MIN(TIME(6,,),MROUND(A1,"1:00"))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question