1. G

    If statement or vba code requirement

    I run a report giving me the shift patterns of colleagues. I need to break down what shift pattern they are on so forexample we have the following. So in the 1st columns I have in A 1and then in B & C I have those starting 1 then 2 in B and in C will bethose starting on 2 then 1 and then the 3...
  2. S

    VBA/Formula Help

    Hi All New to the forum but hopeful someone can help. This is a wok project I have been tasked with but it has me beat. We process payroll weekly but during overtime season it becomes a bit of a pain as our T&A system doesn't deduct a break when employees have done the required amount of...
  3. J

    Formula for deciding shift differential based on a start time

    Hi i'm trying to figure out how to write a formula that would look at a shift start time and based on that time would output the correct shift premium to the cell. So I have 3 scenarios a start time from 05:00 to 13:59 no premium. 14:00 to 21:59 .75 cents per hour and lastly 22:00 to 04:59...
  4. M

    VBA code or Excel functions to shift cells

    Dear All Could you please advise a VBA code or Excel advanced functions to automatically shift entire rows from one area to another. What I mean by area - is that I have 3 areas with 3 different color depending on certain cells values or status of the row. So once a certain cell is updated...
  5. P


    Good morning all I use a spreadsheet to do my shift updates at work but I am finding the other shifts are entering false hours to make things look good. So what I would like to do is pull the hours from the start time and finish times the issue I am having is some production runs finish a...
  6. J

    Lookup multiple cells in a range and sum the results

    Hi, I have a row of shift patterns in cells in cells d33:k33, expresses like this - "0900 - 1700" i also have a secondtable that lists the shift pattern in column 1 and the number of hours forthat shift in column 2 (so for "0900 - 1700" you get 8, and for "0900 - 1300" you get 4... I want to...
  7. B

    Auto populate names to weekly rotating schedule tabs

    Hello all, I am new to this forum, but not new to excel/VBA. I have a problem that I can't seem to work out in my own head, and I believe it's a very simple one which makes it all the more frustrating to me. I have developed rotating shift schedules for my workplace for 6 different...
  8. A

    PERCENTILE with IF condition?

    I am trying to calculate percentiles using an if clause. For exI. want to calculate the 5th percentile weight for all of the women age 19. I am not sure how to write this formula out, also not sure what ctrl shift enter does for arrays? any help appreciated
  9. L

    Control Shift Copy Picking Up Iferror Blank Cells

    Hello - looking for some help / advice on the following. I have added an iferror to my index match to return results of 0 as blank; however, when I control shift copy it picks up all cells with the formula regardless if the cell contains a number or blank. I would like a way to only then copy...
  10. D

    Calender / Roster Help please

    Hi All Need some help please. We are working on 28 day roster. With some off days in between. Then the cycle starts again. It looks like this <tbody> Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We T N N N X D D D X X X N N X X D D D D X N N X X X X X X...
  11. E

    Help with multiple value search and return using index match

    Hi all, First time posting. So I am creating a manpower document for our firehouse. The simple explanation of it is, one master sheet that has everyone's names in column A ad monthly dates in rowA A . At the intersections are drop down menus with specific values to designate on shift, off...
  12. W

    Calculating time difference when start time is 1 day and end time is another

    I have a spreadsheet where workers can calculate downtime simply by entering the start of the downtime and the end of the downtime. This works great until it occurs before midnight and then ends after midnight. How should the formula go to account for the day? I have a start shift and end shift...
  13. A

    VBA - Complicated Code for Keyboard Shortcut to Launch Macro

    Hello all, I am in the process of rewriting several macros authored by several previous authors. The first of three is launched from a button on an Access form. After the first has run, users are asked to paste data into the .xlsx workbook created by the first macro before running the second...
  14. L

    An infinite loop discussion

    G'day! I've designed an automation system which purely runs in vBA to 21 roll formers in our factory (mainly) thanks to each of you for helping along the way! However - I've hit which I believe to be a snag. I run an ENDLESS loop - infinite loop. Checking if (batch complete) - "1" is found...
  15. E

    Premium shift pay formula?

    Hello. I have been going thru the payroll videos and I was trying to see if there is a video on shift premiums? Unless I missed it, maybe? I want to find a formula where a special shift premium is added to the employees pay from 7:00 pm to 1:00 am. The employee may actually start their shift...
  16. B

    Shifting formulated cells based on value of a certain cell

    Hello, I need help with the following please. In row 1 I have a row of currency values, A1 thru A1000. In row 2 I need a formula for each cell B1 thru B? that multiplies its corresponding number in row 1 by a factor (B4), but also looks at A3 to tell it how many cells to the right to shift...
  17. V

    If Formula

    Column A will have time like 09:00 (09:00 is save in text format) Formula in Column B to show Shift one, Shift two or Shift three depending on time shown in column A I tried =if(A1=09:00,"Shift One",if(A1=20:00,"Shift two","Shift Three")) but this formula isn't working. <tbody> Formula to...
  18. T

    Sum with criteria

    Hi I have a range of date cells which cover several years and am currently using the following formula to calculate the number of unique days in a particular year, the dates are in the 01/01/2018 format, in this case 1992...
  19. B

    Sum Product confusion

    Hi Everyone, I have a formula that I'd like to write but i either get #value ! or 0 for the answer but 0 is not the correct answer. This data is on a separate spread sheet. <tbody> A B C D 1 Date Shift Reason Time 2 08/26/18 2 Washing 1:00 3 08/26/18 2 Washing 1:00 4 08/26/18 2...
  20. J

    Multidimensional Array Tutorial / Reads

    I'm having a hard time getting my head around something. Ultimately, I am wanting to feed a range to an array, (for jobs), compare shift capacity to job requirements, and if the job qty is smaller than the shift capacity, add the job to the current shift, and move on to next job. When a job 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