Search code examples
excel-2007

Excel Time Formula

I have the following formula:

=IF(MROUND(((D4-C4+IF(C4>D4,1)-INT(D4-C4+IF(C4>D4,1)))*24), 0.25) < 0.15, 0.25, MROUND(((D4-C4+IF(C4>D4,1)-INT(D4-C4+IF(C4>D4,1)))*24), 0.25))

this formula gets the time between a start and stop time and rounds it up to the nearest 15 minutes. I have a problem with it when no time is entered and it = 15 minutes.

If anyone can help so it says 0 or even a less complex solution that would be great thank, I am thinking a macro what does everyone think?

Solution

  • The formula as written says:

    1. If the first term occurs later than the second one, assume that the first term occurs the next day.
    2. Ignore any day information (invalidating the last point).
    3. If the difference in hours rounded to the nearest fifteen minutes is less than 0.15 of an hour (9 minutes), return fifteen minutes, else return said difference.

    So if you want for it to give zero if neither time is entered, just simplify the formula like this:

    =MROUND(24*MOD(A14-C14,1),0.25)

    which gives zero if the difference is between 0 and 7.5 minutes (Excel just assumes that an empty cell is zero, midnight in this case), otherwise it gives the same results.