Excel Time Comparison and Subtraction

Question

I am trying to do a time subtraction in excel of 30 minutes and I am running into a speed bump. So the table I have are as follows.

Table "Schedule"
Column 1 is day of the week (Mon-Sun) (formated as general, as this is plain text)
Column 2 is start time of the shift (formated as h:mm AM/PM)
Column 3 is end time of the shift (formated as h:mm AM/PM)
Column 4 is duration of the shift (start to end) (formated by formula (TEXT(col3-col2,"h:mm")) )
Column 5 is paid hours (if the total hours is over 6.5 then subtract 0.5 hours for an unpaid lunch) (formula IF(col5>"6:30",col5-"0:30",D5) )

The issue is any time allotment over 10 hours start to end (where column 4, the duration hits 10 hours) no lunch is subtracted at all.

So... Start 9:00 AM, End 6:59 PM, Hours Total 9:59, Hours Paid 9:29

But... Start 9:00 AM, End 7:00 PM, Hours Total 10:00, Hours Paid 10:00

and that should obviously not happen. I can't find anything on google so I figured the excel gurus here may have some advice.

Thanks!

Answer 1

If your time columns are stores using excel's dedicated time format, this should be straightforward. Mixed data types are likely your problem.

First, be sure your time columns (columns 2 and 3) are set using the time function, i.e.,

=time(hours,minutes,seconds)

Then, you should be able to add and subtract easily.

Column 4: = column 3 - column 2

... then subtract 30 minutes also using the time() function:

Column 5: = if(column 4 > time(6,30,0),column 4 -time(0,30,0),column 4)