Hello, I'm trying to make a formula to show the hourly wage based upon a table which tracks pay increases. I can get it to work for the first month, but it needs to work with the range of dates, and thus far all I've gotten is a spill, and I'd prefer it return the specific value. Any help would be appreciated. I attached a sample workbook, which probably makes more sense of what I'm wanting to do that these words.
you wage date starts on the 30th June 22 - But the table goes back to 1st june - so you need some dates which cover the table you are trying to create
what was the salary before 30th june
you table uses 1st as a date
On 21st nov - it goes to 22 - but the date in the table is 1st - do you just want to consider a month ???
also oct shows 22 , i guess that maybe a typo
you are also not showing data after this month - so you want that to be blank - after MAY 23
=INDEX($E$6:$E$16,MATCH(K5,$C$6:$C$16,1))
we can use EOMONTH()
=INDEX($E$6:$E$16,MATCH(EOMONTH(K5,0),$C$6:$C$16,1))
exclude any dates after this month
=IF(EOMONTH(K5,0)>TODAY(),"",INDEX($E$6:$E$16,MATCH(EOMONTH(K5,0),$C$6:$C$16,1)))
regardless of the date in the table - it will check to the end of that month
Last edited by etaf; 05-11-2023 at 10:07 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Here's a 365 way:
=LET(d,Table2[Date],dd,IF(d="","",DATE(YEAR(d),MONTH(d),1)),p,Table2[Pay],vl,CHOOSE({1,2},dd,p),VLOOKUP(K5,vl,2,1))
Your lookup list has October and November the wrong way round!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Updated - this might be required:
=LET(m,MAX(Table2[Date]),i,INDEX(Table2[Days],MATCH(m,Table2[Date],0)),t,m+i,d,Table2[Date],dd,IF(d="","",DATE(YEAR(d),MONTH(d),1)),p,Table2[Pay],vl,CHOOSE({1,2},dd,p),IF(K5<=t,VLOOKUP(K5,vl,2,1),""))
or a SPILL array... delete expected results and use:
=LET(A,FILTER(Table2,Table2[Date]<>""),XLOOKUP(K5:K18,EOMONTH(INDEX(A,,1),-1)+1,INDEX(A,,3),,-1))
Results fill down the column automatically.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
wow thanks. Yea I didn't notice that I messed up the dates. The reason the table on the right only has those two dates is because those are the dates salary changed, the first date is the starting date of the job. I'm trying to comprehend the formula you made. The "Days" column is the number of days between raises so I'm not sure why that column is in your formula, but it works. I tested it by adding the upcoming raise and it all fills out. Thank you very much, I'm going to try and understand your formula now so I can replicate similar things in the future. Again, thanks very very much!
This worked perfectly, just have to figure out how what you did works so I can do it again in other scenarios! thank you for your time!Originally Posted by AliGW
Glad to have helped.
If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks