# Time Subtraction Formula

1. ## Time Subtraction Formula

Hi,

I'm trying to do the following:

In the attached file, I'd like to subtract whichever is the earliest time between B4 or C4 from A4, then B5 or C5 from A5, etc.

I assume I need a conditional "if" statement but I'm not sure if that will work.

Any ideas?

Bill  Register To Reply

2. ## Re: Time Subtraction Formula

=A4-MIN(B4:C4)  Register To Reply

3. ## Re: Time Subtraction Formula

bill? bgates. bill gates? is that really you? i'm a fan of your Excel product. try this:
=A4-MIN(B4:C4)  Register To Reply

4. ## Re: Time Subtraction Formula

Thanks guys, and no, not "that" Bill Gates! That worked fine for that example. But now I'm having other problems.

In the attached example, if you click on B4 and D4 you will see dates linked to those times. If you click on C4 you will see a value in a format similar to the time format but slightly different. This value is also a time. These values are entered automatically by the workplace time clock program. I suspect the different formats are interfering with the calculation.

How can I get around this?

Bill  Register To Reply

5. ## Re: Time Subtraction Formula

You can try this updated function.

=B4-MIN(C4,TEXT(D4,"hh:mm:ss"))  Register To Reply

6. ## Re: Time Subtraction Formula

Hi bgates,

Right click in cell E4, select Format cells--> Click on Custom --->and select hh:mm, then things will work out.

Thanks,
aganesan99  Register To Reply

7. ## Re: Time Subtraction Formula

I couldn't get it to work.

I've formatted the cells correctly and re-written the formula slightly but it seems to be subtracting C from B each time regardless of whether C is a larger value than D (See lines 11, 14, 18 and 24). HELP!  Register To Reply

8. ## Re: Time Subtraction Formula

I should have mentioned, the =B4-MIN(C4:D4) formula works fine using an example with values that are consistent, i.e. all values are either linked to dates in the formula bar or they aren't.

Very puzzling!   Register To Reply