I have an Excel spreadsheet with start time in column A and end time in column B. The formula I came up with displayed the time difference as “Negative dates or times are displayed as ##########. What this means is that the formula is not able to handle times past 12 midnight. I would like the formula to take into consideration time difference past midnight in order to fix the problem.

Here is my formula:

NOTE: The formula works fine for deducting the 30 minutes lunch break if someone works an eight (8) hour shift (for example, 8:00am to 4:30pm; where the 30 minutes is lunch break. So instead of 8:30 minutes, the formula works the way it should (i.e. show 8:00 hours).

Here is my formula:

**=IF(OR(ISBLANK(B3),ISBLANK(A3)),"",B3-A3-(B3-A3>TIME(8,0,0))*TIME(0,30,0))**NOTE: The formula works fine for deducting the 30 minutes lunch break if someone works an eight (8) hour shift (for example, 8:00am to 4:30pm; where the 30 minutes is lunch break. So instead of 8:30 minutes, the formula works the way it should (i.e. show 8:00 hours).

**It also works fine for time difference such as****8:00pm****minus****4:00pm****=****4: 00****. However, if the time difference is****8:30 pm****minus****4:00 pm****, the 30 minutes is part of the solution instead of displaying 4 hours. I want the formula for the time difference to fix this as well.****I have attached a sample copy of the data.**
## Comment