Change in Pay.

[Helix242]

Hi,

Hoping someone can help me. I have a tracker with a list of dates through the year in Column A and in the next 5 Columns the hours are entered for the hours each member of staff works and these are calculated with the hourly rate in the final Column (see below based on one person)

20/09/2017 7 £8.20 £57.40
21/09/2017 7.5 £19.20 £144
22/09/2017 15 £15.00 £225

However I want to be able to input the hourly rate in on cell for each person, and have the ability to amend this one cell to a new hourly rate if required, but for all the previous dates remain with whatever rate was input.

Is this possible??

Thanks in advance!

Mark

 

[istiasztalos]

Hi,

This will be one of those cases when you can use VLOOKUP with the last parameter being TRUE...

I don't know if you have all staff members in one single table or the have the separate sheets... so I used an example where the staff name can be different and each is checked against separate rate table.

The trick is to have a table separately with the dates of rate changes, it is very important that here the dates have to be order from oldest to newest !

In Excel's menu, you can add a fancy looking data Table on top of this, just click anywhere in this little table and go "Insert -> Table". When this is done, click in the Table, you'll have a new menu on the Ribbon called "Table Tools", look for Table name and give it the same name as one of your staff members. In my example it will be "johnny"

[TABLE="class: grid, width: 147, align: left"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]from date[/TD]
[TD]rate[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]8.2[/TD]
[/TR]
[TR]
[TD="align: right"]25/09/2017[/TD]
[TD="align: right"]8.5[/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2017[/TD]
[TD="align: right"]9.1[/TD]
[/TR]
</tbody>[/TABLE]










If this is done, you can use a simple VLOOKUP, it will always take the value from your hourly rate table which is the last one before a new change is reached (notice below that rates change on 25th and 28th as configured in the rate table)
[TABLE="class: grid, width: 460"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]day[/TD]
[TD]staff[/TD]
[TD]hours[/TD]
[TD]rate[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD="align: right"]22/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]=VLOOKUP(A3,INDIRECT(B3),2,TRUE)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]9.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]9.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]9.1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Helix242]

Hi,

Thanks for coming back to me and for the detailed response, however I have a few issues. One is that the tracker has already been built, and I would struggle to add a table without causing issues elsewhere. The other issue is I have and require only one input for the hourly rate. I see the rate on your example listed on each day, but I would require the hourly rate to be input from one cell and for this to be transposed on to the tracker which is laid out similar to your example.

If you can help any further, and if this makes ANY sense, please let me know.

Many thanks

Hi,

This will be one of those cases when you can use VLOOKUP with the last parameter being TRUE...

I don't know if you have all staff members in one single table or the have the separate sheets... so I used an example where the staff name can be different and each is checked against separate rate table.

The trick is to have a table separately with the dates of rate changes, it is very important that here the dates have to be order from oldest to newest !

In Excel's menu, you can add a fancy looking data Table on top of this, just click anywhere in this little table and go "Insert -> Table". When this is done, click in the Table, you'll have a new menu on the Ribbon called "Table Tools", look for Table name and give it the same name as one of your staff members. In my example it will be "johnny"

[TABLE="class: grid, width: 147, align: left"]
<tbody>[TR]
[TD]from date[/TD]
[TD]rate[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]8.2[/TD]
[/TR]
[TR]
[TD="align: right"]25/09/2017[/TD]
[TD="align: right"]8.5[/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2017[/TD]
[TD="align: right"]9.1[/TD]
[/TR]
</tbody>[/TABLE]










If this is done, you can use a simple VLOOKUP, it will always take the value from your hourly rate table which is the last one before a new change is reached (notice below that rates change on 25th and 28th as configured in the rate table)
[TABLE="class: grid, width: 460"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]day[/TD]
[TD]staff[/TD]
[TD]hours[/TD]
[TD]rate[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD="align: right"]22/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]=VLOOKUP(A3,INDIRECT(B3),2,TRUE)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]9.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]9.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2017[/TD]
[TD]johnny[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]9.1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[istiasztalos]

Hi,

The column can be omitted without issues, you just to multiply the worked hours with this formula in the total column.

The problem is having only one source for the hourly rate. Do staff members have different rates, or it is same for all? And most importantly, if you don't track past rates how would Excel calculate older days? (you'd have to manually convert older days from formula to actual values to avoid retroactive changing)

This lookup table can be anywhere in the tracker, even on a hidden sheet, but if you absolutely don't want a formula based rate check, then you'll need VBA script to write the actual number values when you press a button and not have formula based calculations.

 

[Helix242]

Hi,

I'm not adverse to using a formula, I'm just having issues trying to do this with only 1 input per person. This was requested at a later date which didn't help.

So yes there are separate rates for all staff, and yes there is only 1 input per person, so somehow there would ned to be a historic capture of these as I have been told the rate may change during the week/month.

I'm trying to upload an image but it appears to be more tricky that my main issue!!! lol

Thanks<attachment></attachment>

 

[istiasztalos]

Hi,

Yes if you can upload a photo somewhere that can help, especially the part of the workbook where you add the rate inputs per staff. There may be a trick to make it seem it's just one input, but have a table hidden somewhere, if that's acceptable... if not I believe you'll need someone to write a VBA script for you.

 

[Helix242]

Hi,

So all the information on the tracker is taken from the wages input screen, apart from the hourly rate which is input on the main tracker screen. There is also a date range so the user can see what wages and bonus has been paid weekly, or any date range rather than just per month. This would have the added issue of trying to ensure the current and previous hourly rates are correct.

Hope you can help.

Thanks


https://unsee.cc/sitoguba/

 

[istiasztalos]

Hi,

Sorry, I don't see any image at this URL, there's a post here on how to add screenshots from Excel
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Helix242]

Hi,

The images were on the link, I have placed it here too. https://unsee.cc/sitoguba/

Thanks

 

[istiasztalos]

still nothing just takes me to the unsee homepage

Hi,

The images were on the link, I have placed it here too. https://unsee.cc/sitoguba/

Thanks