# 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

2. ## Re: Time Subtraction Formula

Please try this function.

=A4-MIN(B4:C4)

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)

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

5. ## Re: Time Subtraction Formula

You can try this updated function.

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

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

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!

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!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1