# cannot get formula right to subtract time using if

1. ## cannot get formula right to subtract time using if

I have a column titled "event start time"(A1), a column titled " event end time" (B1) and a column titled " total event hours" (C1).
I have the third cell formulated to return the difference in hours, but I'm looking for help with a formula to calculate if the hours = 8 or more, to automatically deduct one hour for break.
If the time is less than 8 hours, to do nothing but just return the actual difference.

2. ## Re: cannot get formula right to subtract time using if

Maybe this?

=IF((B1-A1)>8,B1-A1-1,B1-A1)

3. ## Re: cannot get formula right to subtract time using if

that still just returns 9:00 for me.
When A1 is 08:30 and B1 is 17:30.
(I have it all formatted as military time)

5. ## Re: cannot get formula right to subtract time using if

Try this:

=B1-A1-IF(B1-A1>TIME(8,0,0),TIME(1,0,0),0)

6. ## Re: cannot get formula right to subtract time using if

Here's the sample

7. ## Re: cannot get formula right to subtract time using if

Sorry! Try this:

=IF(TEXT(B1-A1,"hh:mm")>8,B1-A1-VALUE("01:00"),B1-A1)

8. ## Re: cannot get formula right to subtract time using if

Try

=IF((B1-A1)>8/24,(B1-A1)-1/24,B1-A1)

9. ## Re: cannot get formula right to subtract time using if

@63falcondude
this seems to be working, but when I copied the formula down, it returned a #VALUE! for only one of the cells. argghh.
I'll figure that out eventually.
thank you!

10. ## Re: cannot get formula right to subtract time using if

You're welcome. Thanks for the rep!

There is a space in H22. Delete that and it will be working as intended.

11. ## Re: cannot get formula right to subtract time using if

Also, if you want to clean the worksheet up a bit, you can add this (in red) to the existing formula:

=IF(H2&I2="","",I2-H2-IF(I2-H2>TIME(8,0,0),TIME(1,0,0),0))

The formula will return blank if there is nothing in the start or end times.

12. ## Re: cannot get formula right to subtract time using if

perfect! thank you!

13. ## Re: cannot get formula right to subtract time using if

You're welcome. Glad we could help.

Welcome to the forum.

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