Formula required - Everyone if baffled!

[Helix242]

Hi,

I am trying to write a formula that will see the source cell data change every day, but in the recipient cells, the information will only be true to what was input on that day. Please see description below:

A2 - hourly pay rate. This is the only cell this information will be input.

B2:B367 - this will list the dates for the full year i.e.

B2 23/9/2017
B3 24/9/2017
B4 25/9/2017 - and so on until the full year has been listed.

C2 will be the output from A2.

So if we input £10 in A2 we will see £10 in C2.

HERE COMES THE DIFFICULT PART!

On the 24th we change the hourly rate (which is still input in A2) to £15. I require C2 to still show £10, C3 (which is 24/9/2017) to show the new £15 and the remaining days to still show £0 until that date.

So in theory on the 25th we change the hourly rate to £12 (again input is A2 only) I would require to see £10 and £15 in cells C2 and C3 respectively and the new £12 in C4.

Can anyone help please??

Many thanks in advance.

 

[SpillerBD]

You could do it with a macro. Validation of cells to change with Today().
However, I don't see a good audit trail of change dates within the sheets happening and when dealing with Pay, I can't see not having a better audit trail.
I would use a Rate Change Table where Pay Rate and Pay Change data would be entered and used as for VLOOKUP to get the changing data as well as showing those changes.

 

[Helix242]

Hi,

I really appreciate the quick response, however I am unsure with Macros and not too sure about a Rate Change Table. I'm not an idiot and I do have a fairly good knowledge of Excel, I just need a kick start in the right direction if you could help?

Many thanks in advance!

 

[FDibbins]

Instead of having just 1 cell for the rate, I would have all the different rates in a cell, each with a date attached to it. That way, you can test for the "rate date", and then apply it for just those dates that apply to that rate.

You cannot have a cell with $10 in it that applies to, say, 50 rows, but then have $15 in that came cell that will only applies to dates after a certain rate

 

[istiasztalos]

Dude, you reposted your thread from last week ? :D
https://www.mrexcel.com/forum/excel-questions/1023757-change-pay.html

I thought we had something... just kidding !

 

[Helix242]

Sorry mate, I just needed input. I'm going to try your formula later today!

 

[FDibbins]

Sorry mate, I just needed input. I'm going to try your formula later today!

Maybe so, but it is against forum rules to post duplicate threads - it could also waste members time trying to help in 1 thread, when it was solved in another thread

 

[Helix242]

My apologies go out to everyone, this wasn't my intention.