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.
Maybe this?
=IF((B1-A1)>8,B1-A1-1,B1-A1)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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)
Attach a sample workbook, please.
Try this:
=B1-A1-IF(B1-A1>TIME(8,0,0),TIME(1,0,0),0)
Here's the sample
Sorry! Try this:
=IF(TEXT(B1-A1,"hh:mm")>8,B1-A1-VALUE("01:00"),B1-A1)
Try
=IF((B1-A1)>8/24,(B1-A1)-1/24,B1-A1)
@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!
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.
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.
perfect! thank you!
You're welcome. Glad we could help.
Welcome to the forum.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks