1. M

    Best and Easiest to filter in this format

    Hi All I have large data set that appears like this sorted Date - Name - Code - Start - End 01/01/2018 - A - Work - 08:00 - 13:00 01/01/2018 - A - Lunch - 13:00 -13:30 01/01/2018 -A - Work - 13:30 - 16:00 01/02/2018-A - AL - 08:00 - 12:00 01/02/2018 - A- Lunch - 12:00 - 12:30 01/02/2018 - A -...
  2. L

    Total Time Less Lunch Overlap

    Hello is there a way to adjust the formulas in Column I so that if the times overlap the lunch break (12 pm to 12:30 pm) it deducts that time? I have the Lunch In L12 and Lunch Out L13 if that matters. So it should be a 30 minute break. The result of the new formula would then be I5 2.5...
  3. G

    Time issues

    Hello I have a question I'm having problems finding a solution for. I have a time sheet which someone would complete as 07:00 in, 12:00 out for lunch, 13:00 Back from Lunch, 17:00 finish. What Im trying to do is to break down what the person has put in so it shows at what points during the day...
  4. MFish

    Conditional Format with time

    Hi there, I have this VBA Code in my sheet and a module - Option Explicit Dim SchedRecalc As Date Sub Recalc() With Worksheets("Sheet1").Range("B2") .Value = Format(Time, "hh:mm:ss AM/PM") End With Call SetTime End Sub Sub SetTime() SchedRecalc = Now + TimeValue("00:00:01")...
  5. O

    Conditional Formatting - 1 Table - 2 Colors with diff thresholds

    Good morning/afternnon/evening I am managing a team of 30+ agents, based on different schedules, some have 1 hour lunch, and another some have 30 min, the number of agents is changing ( 15-35 ). So I need to find a way to conditional format when a 30 min agent takes more than 30 min an color...
  6. S

    Optimized scheduling to demand based on constraints

    The title kind of states my goal. I am looking for some help to get started to solve the problem of scheduling a group of people based on demand and their individual constraints, using solver and VBA (or maybe a combo of the two?) Here is a picture to describe what I am looking for help to...
  7. R

    Auto Import Specified Data from one sheet t another

    Hi, I would like to accomplish the following; Workbook1 <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style> <colgroup><col style="width: 100px"><col width="113"><col width="100"></colgroup><tbody> #B7B7B7[/URL] , align: center"]Meal...
  8. Taul

    Working hours between two date/times excluding Lunch and weekends, diff time on Friday

    Hi, I need some assistance with calculating working hours between two date/time cells. The working hours are 8:30 to 17:30 and the lunch break is 12:30 to 1:45 The date/time value could be outside the working hours but I only want to count the period within the working hours. I have an...
  9. R

    UserForm Finding a Row with Specific Data, then Populating Data From That Row

    I’m searching for some guidance on creating a timesheet. I thought I would find something on the web,but I couldn’t find anything that really fit what I’m looking to do. Scenario: Tom goes to lunch. He opens theworkbook, and launches a UserForm (UF). Tom completes certain data elements...
  10. S

    Counting how often a time appears

    Hi, I am hoping someone out there will be able to point me in the right direction as I have been searching for ages and can not seem to get anything to work that is not really long winded. A B C D E <tbody>...
  11. M

    Subtotal using formula

    <colgroup><col><col><col><col><col span="2"></colgroup><tbody> Date Items - Nov'18 Qnty Rate Amt. Subtotal 1.11.18 Executive Non Veg Lunch 2 40 80 80 2.11.18 Chicken Biryani 2 95 190 Chicken Bharta 2 45 90 280 3.11.18 Egg Fried Rice 1 36 36 Chilly Chicken Boneless 1 45 45...
  12. K

    How to prevent duplicates.....

    Hi all. A small problem I just can't seem to wrap my head around... I am trying to create a planner and I've tried countif, Datavalidation and conditional formating but when ever I think I am close no dice. My challenge is this: I have X number of people who all have to have lunch together...
  13. J

    Expression Buider

    Hi there! On an Access report, I'd like to add the total hours worked based on 4 fields that produce the following times: 1. initial daily log in, 2. log out for lunch, 3. back in from lunch, and 4. End of Day logout. Can I use the Expression Builder (Design View>Property Sheet>Data...
  14. R

    IF AND OR - Statements

    I've got my statement wrong or I'm trying to write this incorrectly. I have two drop down lists. M5 has 4 statements - Saturday, Sunday, Monday, Tuesday M6 has 3 conditions - Open, Lunch, Closed I am trying write an IF statement. Saturday & Sunday have the same answer for Open & Lunch =...
  15. J

    Coding in Access

    Using Access, I have created a form for employees to log in at the start of their day, log out for lunch, back in from lunch; and lastly, log out at the end of their day. What I want is to create a command that will automatically highlight the next button for them to click, based on their...
  16. O

    IF Statement with Greater Than value

    Hi All I have a rota on Excel that originally referenced a specific cell to determine whether or not to include a lunch break in the total hours cell. I would prefer to simply add this into the IF statement and eradicate the unnecessary extra cell where a lunch break is manually selected...
  17. B

    Rota deducting lunch if shift over so many hours

    So I want to get a total amount of hours worked in a day, which takes away lunch of 30 mins if someone works 6 or more hours, what formula do I use? Cheers
  18. W

    count if sum help in order of time leaving please

    hi can any one help please, really stuggling to get this numbering to work, i have a list of wagons days leaving and need to order them with numbers counting, wagon leaving sum needed to auto count according to time leaving like...
  19. S

    Time Stamp

    Hello everyone! I'm currently working on a Macro-enabled Timesheet for my job where the employees can click a button to record the time they come in to work and leave. I have 4 fields that require a timestamp per day (Clock in for the day, clock out for lunch, clock in from lunch, and clock...
  20. R

    Count if matches criteria?

    I'm sure Excel can do this, and you folks have saved my hide in the past so here we go! I've fussed a bit with countif and vlookup, but I have a new wrench thrown in today. I have a worksheet and I am trying to get it to count if data is entered into one column based on if a specific text is...

Watch MrExcel Video

This Week's Hot Topics

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
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 "".
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