In Excel, if you want to calculate B1 minus A1 in time up to 30 min. If it goes over 30 min, just show 30 min.

Question

For work, we take out 30 minutes drive to our first account at the max. But if its less than 30 minutes, we only take out how long it took. So if it takes an hour, we show 30 min drive time. If it takes 15 min, we only take out 15 minutes for drive time from our daily hours worked. How do I put that into a formula?

Answer 1

=IF((B1-A1)>(30/1440),30/1440,B1-A1)

B1 - Higher Value - time
A1 - Other one

(30/1440) means 30 minutes of a day (24 x60)

The answer should be left in the same time format you have used in A & B. (i guess HH:MM) . Below is how you should type it in the if function formula boxes. (Insert - functions - if)

Argument - (B1-A1)>(30/1440)
If true - 30/1440
If false - B1-A1

Answer 2

B1 and A1 need to be formated as datetime to make this work.

=MIN((B1-A1)*24*60, 30)