Highlight when a time does not meet a set time

Flopem123

New Member
Joined
Aug 31, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Morning

I have a timesheet which is used by around 15 people and I want to ensure people are taking the correct amount of time for lunch.

By law people need to take a break of 20 minutes and I would like to highlight too people if their break is below this legal limit.

One of the problems I have is that people clock in and out multiple times during the day for 5 or 10 minutes but they must have a 20 minute break if working over 6 hours.

DayInOutIn2Out3In4Out5In6Out7In3Out4Hours Worked
Monday08:1010:1010:2012:0012:1516:0007:25
Tuesday06:3007:3010:2012:0012:1518:0008:25
Wednesday08:1010:1010:2012:0012:1516:0007:25
Thursday08:1010:1010:2012:0012:1516:0007:25
Friday08:1010:1010:2012:0012:1516:0007:25
Saturday00:00
Sunday00:00
Total Hours worked this week38:05

The above is just an example of someone who may not take the correct time for lunch and ensure they have a 20 minute break every 6 hours they work on that day.

Is there a simple solution to highlight the Hours worked cell if there is no gap of 20 minutes?

Thanks
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
714
Office Version
  1. 365
Platform
  1. Windows
Your In and Out column headings seem to be out of order--is that what they really look like, or is a copy error?
 

Flopem123

New Member
Joined
Aug 31, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
They are out but it doesn't cause any problems that I know, The formula is all over the place but as it seems to be working I'm reluctant to change it at the moment, I also have loads of numbers after each timesheet but I think it due to have multiple sheets. I will probably fix it when I get some time

=TimeSheet334567891012131415161718[@Out]-TimeSheet334567891012131415161718[@In]+TimeSheet334567891012131415161718[@Out3]-TimeSheet334567891012131415161718[@In2]+TimeSheet334567891012131415161718[@Out5]-TimeSheet334567891012131415161718[@In4]+TimeSheet334567891012131415161718[@Out7]-TimeSheet334567891012131415161718[@In6]+TimeSheet334567891012131415161718[@Out4]-TimeSheet334567891012131415161718[@In3]
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top