I need to add time, but it needs to add in 15 minute increments.

11:00 to 12:00 would be 4 units

11:00 to 11:45 would be 4 units (1 hour min)

11:00 to 12:50 would be 8 units because of the .50 it would round up to the next unit. If it was 44 it would of been units. If there a way to write a formula that would consider all these variances?

Hi, If you have pure Excel times in cells A1 and B1, then the following formula will return the number of quarter hours rounded up as requested. I assume that 11:00 to 11:45 should have been 3. This formula returns 3 for 11:00 to 11:45 but 4 for 11:00 to 11:46.

=IF((B1-A1)/0.0104166666666667=INT((B1-A1)/0.0104166666666667),(B1-A1)/0.0104166666666667,INT((B1-A1)/0.0104166666666667)+1)

Excel stores times as a decimal value, basically from 0 to 1 where 1 is a complete day. 0.5 is 12 noon, i.e., half way through a day.

As Excel also stores dates as the whole number part of the number, if there is any date information stored along with the times, the calculations will be wrong.

You may be able to control data entry into cells using data validation and limit data entry to a decimal value less than 0.999999

This formula will not work for times that cross midnight.

If that is an issue, it should be possible to modify the formula.I don't know how you are planning to use the formula, so I just left it as giving the number of 15 minute periods. You could wrap a x15 around it to get actual minutes.

Note that a value of 15 minutes from the formula is different to Excel's value for a 96th of a day which is 0.01041666667 approx.

Depending on how you are going to use your results from the formula, you may want the result in Excel's decimal values rather than minutes - so multiply by 0.01041666667 instead.

Regards

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History