CODEWITHSUNDEEP
exceltimeexcel-formula
jnjustice
jnjustice

Reputation: 83

Excel Time Comparison and Subtraction

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!

Upvotes: 0

Views: 1102

Answers (2)

Kit
Kit

Reputation: 341

Excel stores time values from 0 to 1. So 24 hours=1, 12 hours=.5 etc. That means 6.5 hours=0.270833333 and .5 hours=0.020833333. As a result you can just do a simple if statement. 

=IF(D2>0.270833333,D2-0.020833333,D2)

To turn it into a time format, is to just use excel's time formating options.

Upvotes: 0

P.J
P.J

Reputation: 488

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)

Upvotes: 1

Related Questions