# 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.
please help.  Register To Reply

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

Maybe this?

=IF((B1-A1)>8,B1-A1-1,B1-A1)  Register To Reply

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)  Register To Reply

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

Attach a sample workbook, please.  Register To Reply

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)  Register To Reply

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

Here's the sample  Register To Reply

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)  Register To Reply

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

Try

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

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!  Register To Reply

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.

If that solved your question, please mark this thread as SOLVED.  Register To Reply

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.  Register To Reply

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

perfect! thank you!  Register To Reply

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

You're welcome. Glad we could help.

Welcome to the forum.  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### Tags for this Thread #### 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