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). 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.
Armando: the formula suggested did work but it displayed 3:30 instead of 4:00 hours for 3pm minus 11am instead of 4 hours. What I want is for the formula to subtract times such as 3pm minus 11am, 4pm minus 11am and so on. However, I want the formula to subtract 30 minutes from the time difference if it is 2:30pm minus 1am.
Those hours in Col C are the y basic hours! Not overtime!
Try this, it will calculate the hours after 8.5, also past midnight: =IF(COUNTA(A3:B3)<2,"",MAX(MOD(B3-A3,1)-(1/24*8.5),0))
There are currently 1 users browsing this thread. (0 members and 1 guests)