Need a formula to count down hours worked vs next pay increase on excel timesheet

[STS-Thomas]

Hello all,

Is there an easy way to track your bi monthly hours worked (including overtime), against your next pay step increase (which is 2080 hours)? I uploaded my timesheet from work, we use Excel.

I created cells N5 - U5 to reflect hours worked before next pay step

Columns E thru M, need to total to Column N, subtracted from N5, unless achieved then move to next step O5 and then next step P5, and so on.

I used 50 hours to track changes faster on this test sheet. I would like to display my countdown to next step in the N column, if possible.

Every employee has a timesheetl that looks like this. I was hoping to learn if there is a formula or formulas that could achieve this.

Anyone know?

Thanks in advance,

Thomas

[Beamernsw]

Hi Thomas, Not exactly sure what you want, I may have it wrong....but, I used the following formula in cell N5 and then used a similar one in each "Step" cell following using it's new required TOTAL.

Please Login or Register  to view this content.

This formula starts inside by getting the MAXIMUM of either 50-Q25 or 0 (that is 50-116=-66 or 0 .... answer: 0 is greater then -66)
Then it looks for the MINIMUM of 50 or the other value, which in this case is 0. Answer: 0 is less then 50.

Move up a couple of cells to P5 and formula is =MIN(150,MAX(150-$Q$25,0))
Starts inside by getting the MAXIMUM of either 150-Q25 or 0 (that is 150-116=34 or 0 .... answer: 34 is greater then 0)
Then it looks for the MINIMUM of 150 or the other value, which in this case is 34. Answer: 34 is less then 50.
34 hours to go before next level.



Also, could a VLOOKUP table do what you want? They can be very handy.

[STS-Thomas]

Hi!

I downloaded this and looked at your amazing formulas and it works nice but not exactly what I was looking for.

We are asking our employer to give us credit for hours worked, including overtime hours at a 1 to 1 value. We agreed to convert the 1 or 2 year steps to hours. 2080 hours, for the first pay increase and 4160 for the next.

What I was looking for was an easy way to track when the first step is achieved and then the next step starts counting backwards. Right now, all the steps count backwards.

Also, is there a way to visualize exactly when the next step is achieved so only those hours after the step are paid at the higher rate, not all the hours on that pay period.

I don't know how you can make these formulas, but thank you for trying!

It seems close, but I don't know how to fully articulate what I am looking for. I hope this is enough to point you or anyone else closer to what I am looking for!

Thank you very much for your help.

Thomas

[STS-Thomas]

Ok, I am dumb. I realized that your cell formulas simply added each value and so that part would work fine! How would you expand these formulas across several sheets? We have folders for each year, and 52 sheets in each folder for every employee. How would you make this formula work over multiple sheets and then carry over multiple folders?

The only other issue is some indicator as to exactly what hour amount is carried over into the next higher pay step. But this formula would work for the first part, I think.

Thanks again,

Thomas

[Beamernsw]

Hi Thomas, could you simply add a Running Balance figure to a cell that brings forword the previous sheet's running total?

I've added an example in this version plus added a line showing the "Next Step" values which might help make it easier for you to visualise...maybe
You can see that each sheet brings over the previous sheet's total and adds Q26 to it to give a running balance.

In Sheet "Feb 16-29", Cell V5, instead of ='Pretend previous workbook'!V5+Q26 you would have something like =[Last Workbookname]'Feb 1-15'!V5+Q26

Also, in Sheet "Mar 16-31", I've added a HLOOKUP to provide the current step and hours until next step and hidden the rows with the RED numbers and just show those 2 numbers from the lookup just as another idea. Select the rows and click unhide to see how the lookup works.

Oh, BTW, I've increased the steps from 50 - 100 so that it carried across sheets better.

Edit: Moved hlookups to a different sheet.
Edit2: Sorry, I had a misktake in my formulas

[STS-Thomas]

Sorry, was off for a few days.

This looks really great! I should send you a Venmo payment for lunch at the minimum! The would have taken me months or years to figure out!


Ok, so I downloaded this and showed my co-workers and asked their thoughts and I was wondering if you have any thoughts on how to accomplish this. Sometimes employees will get promoted to a higher rate of pay and the hours would need to reset back to zero. Conversely, if an employee changes status, they may downgrade rate of pay and alos need to reset back however, their last hours would continue when they had changed status. Is there a way to denote this? Also someone suggested to move the "Next Step" cells from M column to C column as we occassionally add additional columns on the back half of our timesheets.

Another request was since we organize all timesheets by each calendar year for the group of 40 employees, should there be a way to manually stop and restart the hours on the first timesheet of January?

Thanks again for all your help!

Thomas

[Beamernsw]

Sorry, I never got notified of your last post, I just happened to check-in to see if I missed anything....which I did
I'm really busy these next few days, but I'll have a look and a think over the weekend.
If any gurus here would like to chime in, please do so

[Beamernsw]

Hi again...

I'm thinking this pay rate would need to be checked daily? If so, then maybe insert another column next to column A which would count the running total. A "Pay Code" could be entered for each day in column A and when the "Pay Code" changes then only those hours from that day forward would be counted. The running total would always be a total of hours worked (Column Q) since the last Pay Code change. For January 1, the total would start at 0 plus that days hours.

Is that along the lines of what you were thinking?

If the pay rate only changed fortnightly then we could forget adding those columns in and just have a single cell with the "Pay Code" and it could check to see if the code is different to the previous fortnight and reset the running total to just the hours from that fortnight.

Depends whether the pay rate could change mid-fortnight or not I guess.

Edit: Forget that last bit, I just realised the sheets aren't a fortnight anyway .... well ... unless the employees always get paid on the 1st and 16th of the month, then that method would still stand.