Conditional Time differences....

chpassage

New Member
I am working on a work schedule and have come across a problem. I want to be able to add a condition to a formula that "If an empoyee works 6 or more hours in one day, then 1/2 an hour (00:30) will automatically be subtracted from the total for that day. (On the other hand, and it may be easier said this way) if the employee works less than 6 hours, I don't want the 1/2 hour to be taken out. I am currently using the formula =(B1-A1) where B1 is the "Ending Time" and A1 is the "Starting Time" --- The problem is that an employee doesn't work over 6 hours every day, and therefore doen't take a lunch period. The schedule may change from week to week or day to day. I appreciate any help!

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

chpassage

New Member
I figured it out..... Assuming A1=Time In & B1=Time Out......

<<< =IF(B1-A1<0.25,B1-A1,(B1-A1)-0.020833)*24 >>>

I was forgetting that I needed to use the decimal conversions for the time values....... 0.25 (6 hours) is my desired threshold, and 0.020833 is the numerical equivelent to 30 mins. This would change if the lunch period was different (ie:0.03125 for 45 mins or 0.041667 for an hour). Multiplying the entire condition by 24 allows me to see the result as say 7.50 hours instead of 7:30.....This cell should be formated "general" and have 2 decimal places to be displayed correctly.

Replies
3
Views
1K
Replies
4
Views
720
Replies
1
Views
681
Replies
1
Views
133
Replies
4
Views
441

1,191,119
Messages
5,984,758
Members
439,909
Latest member
daigoku

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back