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

 

[Helix242]

Hi,

Sorry the images are deleted after 24 hours. I have uploaded 3 images for you.

• Where the hours for each staff member are entered
• The main tracker page where the hourly rate is entered (BW6) and this also shows the outputs
• I think I have managed to create a formula, albeit very crude, but it looks like it MIGHT be working, you will understand when you see the formula as I will have to wait until tomorrow. This formula had to be written for all 14 staff.

If you can help at all it would be appreciated. I am sure there would be an easier VBA workaround, however I am not that good at the VBA in Excel.

Thanks in advance

https://unsee.cc/morepusa/

 

[istiasztalos]

weird, i still don't have anything at this unsee page- There is a timer that thells me it will be deleted in 22 hours, but there's no photo or even a placeholder for image, just a gray background

let's see if your formula works, if not you can paste it here to see if I can help maybe

 

[Helix242]

That is odd. I have tried to explain the issue and formula as best I can below:

Okay, so A2 - the dates currently on 25/9/2017 - these dates were input manually and not obtained via a formula.


A2
25/9/2017
26/9/2017
27/9/2017 - etc


‘BONUS & PAY’!$BW$6 - is where the hourly rate is entered, there is one for every 14 staff.


‘BONUS & PAY’!$BW$6 ‘BONUS & PAY’!$BX$6 ‘BONUS & PAY’!$BY$6 - etc


C3 is where the hours are entered for each staff, this is on the WAGES tab, the one I am currently talking about and where this formula is located.


A2 C3 D3 E3 - etc


25/9/2017 £10 £15 £9
26/9/2017 £8 £12 £8
27/9/2017 £10 £15 £8


I need to ensure that the hourly rate can be changed each day from the single location ob ‘BONUS & PAY’! $BW$6 ( and sequential for each member of staff). The formula I have come up with is:


Q2 - =IF($A2=TODAY(),'PAY & BONUS'!$BW$6,Q2)
R2 - =IF(S2=T2,Q2,'PAY & BONUS'!BW$6)
S2 - =($A2-1)
T2 - =S2
U3 - =C2*R3


There is a feedback loop which Excel classes as an error in Q2, however this does not stop the formula from working. If this can be simplified in ANY way it would be appreciated.

Many thanks

 

[istiasztalos]

Hi,

Referring a cell to itself is dangerous, but it can serve the purpose if there are no other options. (risk is all the other cells referencing anything that comes from that cell will no longer update, even if the related contents are visibly changed, for example changing the worked time for yesterday's row will not update the total pay column even though its formula is based on said worked hours)

If you really want to do this way though, R S and Q are not needed, S2 is always same as T2 so the IF in R2 is always TRUE and therefore Q2

 

[Helix242]

I understand. I have run this for 3 days and it appears to be updating okay.

I am unsure what you mean with your formula change as the hourly rate just keeps changing to the new one, instead of leaving the previous cell the same as the previous day an not updating it.

This is the issue I have.

 

[Helix242]

Okay, I understand that the cell contents (in the feedback loop) don't work when calculating a total However I am still unsure how your amended formula works.

Sorry to be a pain, but if you could offer some help on this it would be appreciated.

Thank you

 

[Helix242]

Hi,

Sorry, this makes no sense at all. You say Q is not needed, but then you reference this in your formula, and there is no reference at at all to 'BONUS & PAY'!$BW$6 - which is where the data is taking from.

Can I presume you have completely misunderstood my initial requirement, or am I misreading your answer. Either way I would appreciate it if you could come back to me.

Many thanks

 

[istiasztalos]

Hi Helix,

Q is needed, the others not (R, S, T)

I have this setup:

[TABLE="class: grid, width: 688"]
<colgroup><col><col><col><col span="13"><col><col span="3"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]Dates[/TD]
[TD][/TD]
[TD]hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]hourly[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD]26/09/2017[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(A4=TODAY(),hourlyrate,Q4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=C4*Q4[/TD]
[/TR]
</tbody>[/TABLE]

I tested in Excel 2010, if new have newer there's change that it actually behaves somewhat differently.

It works for me as long as I put in the hours on the actual day; changing retroactively, if for example I were to change the hours for yesterday the formulas don't update that row at all.

 

[Helix242]

Hi,

Okay, I think I understand and thank you. You reference the FALSE destination to be Q4, is this referencing itself as you told me not to do this? I presume it was as you have c4*Q4 in the final column.

Thank you for your help with this.

 

[istiasztalos]

exactly ! though I maintain it's quite risky

Hi,

Okay, I think I understand and thank you. You reference the FALSE destination to be Q4, is this referencing itself as you told me not to do this? I presume it was as you have c4*Q4 in the final column.

Thank you for your help with this.