VLOOKUP that will SUM data based on Date Range

U

UnitedCloud01

New Member
Joined
Nov 14, 2017
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi Team

I have a budget forecast spreadsheet that I would like some help with please. I use the Australian Financial Year - 01st July to 30th June.

I currently have workers that have a pay point increase on the anniversary of their commencement date. I calculate the days prior and post the anniversary or commencement date to calculate their annual salary. At the beginning of each financial year, the award amount increases with government legislation and / or consumer price index and I amend the Pay Table tab accordingly. The VLOOKUP accesses this table for the calculations.

There is another group of workers that, in addition to increasing a pay point after the anniversary of their commencement date, also experience an increase in the pay amount during the financial year - currently 17/10/2024 (as opposed to the above which occurs at the beginning of the financial year). These are new positions and are not accommodated by my current formula.

I am trying to create a formula that incorporates what I have already written and works well, with amendments that allow me to accommodate workers who are covered by the latter award.

I have attached a mini sheet. The Staffing and Vehicles page is where the workers are. The HP award (under QHEALTH HP in the Pay Table) is the one not accommodated by my current formulas.

BUD - Mini Sheet.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBP
1Program name17/10/2024Award Date Change (QHealth)
2LEGEND: Highlighted cells calculate automaticallySurplus/Deficit1976Ordinary Hours (38 hours X 52 weeks)
3*Includes Super
4Salary and wages positions01/07/202430/06/2025DO NOT CHANGE6 - 16606 - 16806 - 16106 - 1690
5FTEPositionAnniversary or Commencement (delete Days before if commencing)# Days before Anniversary# Days after Anniversary / CommencementLevelsalaryLevelsalaryVehiclesalary sacrifice per FTESalaryNameStart DateEnd DateContract End DateWork TypeSuperWorkercoverAnnual*LSL OncostsTotal Total by Month MonthHourly RateMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFriday
6BudgetedActual Per annumChoose from drop down listChoose from drop down listIf vehicle provided, should be same as FTE $ 6,000.0011.5%1.50%17.50%1.35%HoursFTEStartBreakEndHoursStartBreakEndHoursStartBreakEndHoursStartBreakEndHoursStartBreakEndHoursStartBreakEndHoursStartBreakEndHoursStartBreakEndHoursStartBreakEndHoursStartBreakEndHours
71.01.0Centre Manager01/09/202463302HP5.2$ 25,352HP5.2$ 121,531$ -$ 146,883.00Permanent$ 17,118.93$ 2,456.62$ 1,977.27$ 2,210.96$ 170,646.78$ -$74.08761.009:0000:3017:007.5009:0000:3017:157.7509:0000:3017:007.5009:0000:3017:157.7509:0000:3017:007.5009:0000:3017:007.5009:0000:3017:157.7509:0000:3017:007.5009:0000:3017:157.7509:0000:3017:007.50
81.01.0Practice Manager29/08/202460305SCH4.3$ 14,727SCH4.4$ 76,541$ -$ 91,268.21Permanent$ 10,637.13$ 1,526.46$ 1,228.61$ 1,373.81$ 106,034.22$ -$46.36761.009:0000:3017:007.5009:0000:3017:157.7509:0000:3017:007.5009:0000:3017:157.7509:0000:3017:007.5009:0000:3017:007.5009:0000:3017:157.7509:0000:3017:007.5009:0000:3017:157.7509:0000:3017:007.50
92.02.00.0$ 40,079$ 198,0720$ -$ 238,151.21
10
1. Staffing & vehicles
Cell Formulas
RangeFormula
E7:E8E7=IF(D7="",0,IF(AND(D7="",O7=""),0,IF(O7=D7,0,IF(AND(D7<>"",O7="",P7<>"",P7<D7),P7-$D$4+1,IF(AND(D7<>"",O7<>"",P7="",O7<D7),D7-O7+1,IF(AND(D7<>"",O7<>"",P7<>"",O7<D7,P7>D7),D7-O7,IF(AND(D7<>"",O7<>"",P7<>"",O7<D7,P7<D7),P7-O7,IF(AND(D7<>"",O7=""),IF(D7=$D$4,D7-$D$4,(D7-$D$4+1))))))))))
F7:F8F7=IF(AND(D7<>"",O7<>"",P7<>"",O7<D7,P7<D7),0,IF(AND(D7<>"",O7<>"",P7<>"",O7<D7,P7<$E$4),P7-D7+1,IF(AND(D7<>"",O7<>"",P7<>"",O7=D7,P7<$E$4),P7-D7+1,IF(AND(D7<>"",O7<>"",O7<D7),$E$4-D7,IF(AND(D7<>"",O7<>"",O7=D7),$E$4-D7+1,IF(AND(D7="",P7=""),0,IF(AND(D7<>"",P7="",E7<>0),$E$4-D7,IF(AND(D7<>"",P7="",E7=0),$E$4-D7+1,IF(AND(D7<>"",P7<>""),IF(P7<=D7,0,P7-D7))))))))))
L7:L8L7=K7*-6000
M7:M8M7=IF(AND(R7="Casual",E7<>"",F7=0),Z7*1976*(B7/365)*E7,IF(AND(R7="Casual",E7<>"",F7<>""),Z7*1976*(B7/365)*(E7+F7),IF(AND(R7="Casual",E7=0,F7<>""),Z7*1976*(B7/365)*F7,H7+J7+L7)))
S7:S8S7=IF(R7="Casual",(M7)*$S$6,((M7)+(M7)/52*4*$U$6)*$S$6)
T7:T8T7=(M7+((M7)*$S$6))*$T$6
U7:U8U7=(((M7)/52*4)*$U$6)
V7:V8V7=((M7)+((M7)*$S$6))*$V$6
W7:W8W7=IF(R7="Casual",SUM(M7,S7:T7,V7),SUM(M7,S7:V7))
X7:X8X7=IF(P7<>"",((W7/(DATEDIF($D$4,P7,"M"))*Y7)),(W7/12)*Y7)
Z7:Z8Z7=IF(D7="","",IF(R7="Casual",IF(I7<>"",IF(TODAY()<$M$1,IFERROR((VLOOKUP(I7,'Pay Table'!$A$4:$F$83,6,FALSE)),0),IFERROR((VLOOKUP(I7,'Pay Table'!$H$4:$M$83,6,FALSE)),0)),IF(TODAY()<$M$1,IFERROR((VLOOKUP(G7,'Pay Table'!$A$4:$F$83,6,FALSE)),0),IFERROR((VLOOKUP(G7,'Pay Table'!$H$4:$M$83,6,FALSE)),0))),IF(I7<>"",IF(TODAY()<$M$1,IFERROR((VLOOKUP(I7,'Pay Table'!$A$4:$F$83,5,FALSE)),0),IFERROR((VLOOKUP(I7,'Pay Table'!$H$4:$M$83,5,FALSE)),0)),IF(TODAY()<$M$1,IFERROR((VLOOKUP(G7,'Pay Table'!$A$4:$F$83,5,FALSE)),0),IFERROR((VLOOKUP(G7,'Pay Table'!$H$4:$M$83,5,FALSE)),0)))))
AA7:AA8AA7=IF(SUM(AF7,AJ7,AN7,AR7,AV7,AZ7,BD7,BH7,BL7,BP7)=0,"",SUM(AF7,AJ7,AN7,AR7,AV7,AZ7,BD7,BH7,BL7,BP7))
AB7:AB8AB7=IF(SUM(AF7,AJ7,AN7,AR7,AV7,AZ7,BD7,BH7,BL7,BP7)/76=0,"",SUM(AF7,AJ7,AN7,AR7,AV7,AZ7,BD7,BH7,BL7,BP7)/76)
B7:B8B7=AB7
J7:J8J7=IF(D7="",0,IF(R7="Casual",0,IF(TODAY()<$M$1,(IFERROR((VLOOKUP(I7,'Pay Table'!$A$3:$B$83,2,FALSE)),0))*B7/365*F7,(IFERROR((VLOOKUP(I7,'Pay Table'!$H$3:$I$83,2,FALSE)),0))*B7/365*F7)))
AF7:AF8,BP7:BP8,BL7:BL8,BH7:BH8,BD7:BD8,AZ7:AZ8,AV7:AV8,AR7:AR8,AN7:AN8,AJ7:AJ8AF7=IF(OR(AC7="",AE7=""),"",(TEXT((TEXT(INT(AE7/100)/24+MOD(AE7,100)/1440,"hh:mm")-TEXT(INT(AC7/100)/24+MOD(AC7,100)/1440,"hh:mm"))-TEXT(INT(AD7/100)/24+MOD(AD7,100)/1440,"hh:mm"),"hh:mm"))*24)
A9:B9,J9:M9,H9A9=SUM(A7:A8)
C9C9=(A9-B9)
H7:H8H7=IF(D7="",0,IF(R7="Casual",0,IF(TODAY()<$M$1,(IFERROR((VLOOKUP(G7,'Pay Table'!$A$3:$B$83,2,FALSE)),0))*B7/365*E7,(IFERROR((VLOOKUP(G7,'Pay Table'!$H$3:$I$83,2,FALSE)),0))*B7/365*E7)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C9Cell Value>0textNO
C9Cell Value=0textNO
C9Cell Value<0textNO
AC7:AC9,AE7:AE9,AG7:AG9,AI7:AI9,AK7:AK9,AM7:AM9,AO7:AO9,AQ7:AQ9,AS7:AS9,AU7:AU9,AW7:AW9,AY7:AY9,BA7:BA9,BC7:BC9,BE7:BE9,BG7:BG9,BI7:BI9,BK7:BK9,BM7:BM9,BO7:BO9Cell Value<>""textNO
Cells with Data Validation
CellAllowCriteria
G7:G8List='Pay Table'!$A$85:$A$161
I7:I8List='Pay Table'!$A$85:$A$161
K7:K8Any value
Y7:Y8List1,2,3,4,5,6,7,8,9,10,11,12
R7:R8ListPermanent, Max Term, Casual


BUD - Mini Sheet.xlsx
ABCDEFGHIJKLMNO
1PAY TABLES
2Level 24/25 Budget Salary 24/25 Weekly Rate 24/25 F/ntly Rate24/25 Hourly Rate24/25 Hourly Casual Rate 24/25 Budget Salary 24/25 Weekly Rate 24/25 F/ntly Rate24/25 Hourly Rate24/25 Hourly Casual Rate 23/24 Budget Salary
3SCHADS AWARD3.75%Increase from 23/241.25SCHADS AWARD
4SCH1.1$ 50,211$ 965.60$ 1,931.20$ 25.41$ 31.76SCH1.1$ 50,211$ 965.60$ 1,931.20$ 25.41$ 31.76$ 48,396
5SCH1.2$ 51,830$ 996.73$ 1,993.45$ 26.23$ 32.79SCH1.2$ 51,830$ 996.73$ 1,993.45$ 26.23$ 32.79$ 49,956
6SCH1.3$ 53,680$ 1,032.31$ 2,064.63$ 27.17$ 33.96SCH1.3$ 53,680$ 1,032.31$ 2,064.63$ 27.17$ 33.96$ 51,740
7SCH2.1$ 66,034$ 1,269.88$ 2,539.76$ 33.42$ 41.77SCH2.1$ 66,034$ 1,269.88$ 2,539.76$ 33.42$ 41.77$ 63,647
8SCH2.2$ 68,104$ 1,309.70$ 2,619.40$ 34.47$ 43.08SCH2.2$ 68,104$ 1,309.70$ 2,619.40$ 34.47$ 43.08$ 65,643
9SCH2.3$ 70,175$ 1,349.52$ 2,699.04$ 35.51$ 44.39SCH2.3$ 70,175$ 1,349.52$ 2,699.04$ 35.51$ 44.39$ 67,638
10SCH2.4$ 72,041$ 1,385.39$ 2,770.79$ 36.46$ 45.57SCH2.4$ 72,041$ 1,385.39$ 2,770.79$ 36.46$ 45.57$ 69,437
11SCH3.1$ 73,804$ 1,419.30$ 2,838.60$ 37.35$ 46.69SCH3.1$ 73,804$ 1,419.30$ 2,838.60$ 37.35$ 46.69$ 71,136
12SCH3.2$ 75,915$ 1,459.91$ 2,919.82$ 38.42$ 48.02SCH3.2$ 75,915$ 1,459.91$ 2,919.82$ 38.42$ 48.02$ 73,171
13SCH3.3$ 77,555$ 1,491.45$ 2,982.90$ 39.25$ 49.06SCH3.3$ 77,555$ 1,491.45$ 2,982.90$ 39.25$ 49.06$ 74,752
14SCH3.4$ 79,134$ 1,521.81$ 3,043.61$ 40.05$ 50.06SCH3.4$ 79,134$ 1,521.81$ 3,043.61$ 40.05$ 50.06$ 76,274
15SCH4.1$ 85,120$ 1,636.93$ 3,273.85$ 43.08$ 53.85SCH4.1$ 85,120$ 1,636.93$ 3,273.85$ 43.08$ 53.85$ 82,044
16SCH4.2$ 87,355$ 1,679.90$ 3,359.80$ 44.21$ 55.26SCH4.2$ 87,355$ 1,679.90$ 3,359.80$ 44.21$ 55.26$ 84,197
17SCH4.3$ 89,589$ 1,722.87$ 3,445.75$ 45.34$ 56.67SCH4.3$ 89,589$ 1,722.87$ 3,445.75$ 45.34$ 56.67$ 86,351
18SCH4.4$ 91,598$ 1,761.51$ 3,523.02$ 46.36$ 57.94SCH4.4$ 91,598$ 1,761.51$ 3,523.02$ 46.36$ 57.94$ 88,288
68QHEALTH HP17/10/2023QHEALTH HP17/10/2024
78HP4.1$ 124,435$ 4,769.60$ 62.76$ 77.19HP4.1$ 128,169$ 4,912.70$ 64.64$ 79.51
79HP4.2$ 127,031$ 4,869.10$ 64.07$ 78.80HP4.2$ 130,843$ 5,015.20$ 65.99$ 81.17
80HP4.3$ 130,350$ 4,996.30$ 65.74$ 80.86HP4.3$ 134,261$ 5,146.20$ 67.71$ 83.29
81HP4.4$ 133,901$ 5,132.40$ 67.53$ 83.06HP4.4$ 137,918$ 5,286.40$ 69.56$ 85.56
82HP5.1$ 140,767$ 5,395.60$ 70.99$ 87.32HP5.1$ 144,991$ 5,557.50$ 73.13$ 89.94
83HP5.2$ 146,883$ 5,630.00$ 74.08$ 91.12HP5.2$ 151,289$ 5,798.90$ 76.30$ 93.85
Pay Table
Cell Formulas
RangeFormula
B4:B18B4=O4*(1+$B$3)
C4:C18,J4:J18C4=E4*38
D4:D18,K4:K18D4=E4*76
E4:E18,L4:L18E4=B4/1976
F4:F18,M4:M18F4=E4*$F$3
I4:I18I4=O4*(1+$B$3)


Thanks so much for considering helping me.

Scott
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
There are a lot of formulas here. Which cell(s) is it that you are trying to get an updated formula for?
 
Upvote 0
myall_blues said:
There are a lot of formulas here. Which cell(s) is it that you are trying to get an updated formula for?
Click to expand...
Yes...good point!! DOH. O_o

On the Staffing & vehicles tab, Column H and Column J contain the VLOOKUP formulas that currently calculate the salary for the worker based on the anniversary of commencement / commencement date of employment.

This part of the formula: IF(TODAY()<$M$1 part was my attempt to account for the award date change but then realised this would not allow me to properly calculate the salaries as the calculations would merely use one data set prior to the date and then change after the date.

The Pay Tables tab contains the wages data. The QHEALTH HP rates are for 17/10/2023 and 17/10/2024. I duplicated the other awards above this as I was trying to make it easier to make a simpler formula for myself but...

Hope this helps clarify.

Thank you for looking.

Scott
 
Upvote 0
No worries. Can you tell me what your expected results are in H7:H8 and J7:J8.
Let's say the date change of the award was today (i.e the value in M1) - what would the results be?
 
Upvote 0
myall_blues said:
No worries. Can you tell me what your expected results are in H7:H8 and J7:J8.
Let's say the date change of the award was today (i.e the value in M1) - what would the results be?
Click to expand...
Hi again

The results for row 8, Practice Manager, would not change as it is a different award.

The results for row 7, Centre Manager, would now be H7 - $26,113 and J7 - $125,176. The problem is that it applies the change of award in a blanket fashion rather than acknowledging that the period of July 1 to October 16 is the previous award.

Hope this helps?

Thanks so much.

Scott
 
Upvote 0
I had some trouble getting my head around it (I think a lot of your formulas could be simplified substantially) but at a basic level I don't think you've given yourself enough information to work it out in a single formula.
For a payrate that changes twice in a year you need to calculate three numbers:
  • The number of days from the start of the year to whichever is the minimum of the anniversary date or the award date.
  • The number of days between the minimum of the anniversary date/award date and the maximum of those two.
  • The number of days from the maximum of the anniversary date/award date and the end of the year.
Once you have those three figures then you can apply your VLOOKUP in the same way to get the salary for that part of the year (you will need an extra column for the extra salary calculation).
In the example shown below I calculated the three points above in E21:G23. In the formulas $N$1 contains the award change date and $D$4 contains the date for the start of the FY, per your original sheet.

United101.xlsx
ABCDEFGHIJKLMNOPQ
15PositionAnniversary or Commencement (delete Days before if commencing)LevelsalaryLevelsalaryVehiclesalary sacrifice per FTESalaryNameStart DateEnd Date
16Choose from drop down listChoose from drop down listIf vehicle provided, should be same as FTE 6000
17Let (A) = Anniversary date
18Let (B) = award date change
19Let (C) = Start FY, (D) =EOFY
20MIN(A,B)-CMAX(A,B)-MIN(A,B)MAX(A,B)-D
21Centre Manager1/09/20246346256
22Practice Manager29/08/20246049256
23Centre M211/01/202510986170
24
Sheet2
Cell Formulas
RangeFormula
E21:E23E21=MIN(MAX($D21,$P21,$Q21),$N$1)-DATE(YEAR($D$4),7,0)
F21:F23F21=MAX(MAX($D21,$P21,$Q21),$N$1)-MIN(MAX($D21,$P21,$Q21),$N$1)
G21:G23G21=DATE(YEAR($D$4)+1,7,0)-MAX(MAX($D21,$P21,$Q21),$N$1)
 
Upvote 0
myall_blues said:
I had some trouble getting my head around it (I think a lot of your formulas could be simplified substantially) but at a basic level I don't think you've given yourself enough information to work it out in a single formula.
For a payrate that changes twice in a year you need to calculate three numbers:
  • The number of days from the start of the year to whichever is the minimum of the anniversary date or the award date.
  • The number of days between the minimum of the anniversary date/award date and the maximum of those two.
  • The number of days from the maximum of the anniversary date/award date and the end of the year.
Once you have those three figures then you can apply your VLOOKUP in the same way to get the salary for that part of the year (you will need an extra column for the extra salary calculation).
In the example shown below I calculated the three points above in E21:G23. In the formulas $N$1 contains the award change date and $D$4 contains the date for the start of the FY, per your original sheet.

United101.xlsx
ABCDEFGHIJKLMNOPQ
15PositionAnniversary or Commencement (delete Days before if commencing)LevelsalaryLevelsalaryVehiclesalary sacrifice per FTESalaryNameStart DateEnd Date
16Choose from drop down listChoose from drop down listIf vehicle provided, should be same as FTE 6000
17Let (A) = Anniversary date
18Let (B) = award date change
19Let (C) = Start FY, (D) =EOFY
20MIN(A,B)-CMAX(A,B)-MIN(A,B)MAX(A,B)-D
21Centre Manager1/09/20246346256
22Practice Manager29/08/20246049256
23Centre M211/01/202510986170
24
Sheet2
Cell Formulas
RangeFormula
E21:E23E21=MIN(MAX($D21,$P21,$Q21),$N$1)-DATE(YEAR($D$4),7,0)
F21:F23F21=MAX(MAX($D21,$P21,$Q21),$N$1)-MIN(MAX($D21,$P21,$Q21),$N$1)
G21:G23G21=DATE(YEAR($D$4)+1,7,0)-MAX(MAX($D21,$P21,$Q21),$N$1)
Click to expand...
Hi Again

Thanks so much for your reply and time thinking about my problem. I do concede that my formulas are likely not very streamlined. I have taught myself by having a need and then searching forums for solutions and making them work. DOH.

In relation to the solution that you have provided, I have input E, F, and G. The only problem is that the new formula does not manage the impact of a start date or an end date like I need. If the start date in column P is the same as the Anniversary or Commencement Date in Column D, then there are 0 days in Column E before the Anniversary / Commencement Date and the beginning of the financial year in July 1st. Likewise, if the end date in Column Q is prior to the end of the financial year, Column G with the days after Anniversary / Commencement will have adjusted accordingly. I should have provided better narrative to go with the other columns. Sorry.

I relation to the addition of a new Salary Column, am I unsure of where to add it or what column it should reference. Sorry.

Thanks so much for helping me.

Chat soon

Scott
 
Upvote 0
All good - we're getting closer

Something like this maybe? You will no doubt still need to make adjustments based on start dates etc. Note the third lookup formula is different to the first two but that might need an IF.

United101.xlsx
ABCDEFGHIJKLMNOPQRS
17Let (A) = Anniversary date
18Let (B) = award date change
19Let (C) = Start FY, (D) =EOFYLevelsalaryLevelsalaryLevelsalary
20MIN(A,B)-CMAX(A,B)-MIN(A,B)MAX(A,B)-D
21Centre Manager1/09/2024046256HP5.20.00HP5.218511.28HP5.2106109.55124620.831/09/2024
22Practice Manager29/08/20246049256SCH4.314727.02SCH4.312027.07SCH4.464244.4090998.49
23Centre M211/01/202510986165HP5.243863.69HP5.234608.05HP5.268390.92146862.6625/06/2025
Sheet2
Cell Formulas
RangeFormula
E21:E23E21=IF(R21=D21,0,IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),MIN(MAX($D21,$R21),$P$1)-DATE(YEAR($D$4),7,0),MIN(MAX($D21,$R21,$S21),$P$1)-DATE(YEAR($D$4),7,0)))
F21:F23F21=IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),MAX(MAX($D21,$R21),$P$1)-MIN(MAX($D21,$R21),$P$1),MAX(MAX($D21,$R21,S21),$P$1)-MIN(MAX($D21,$R21,S21),$P$1))
G21:G23G21=IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),S21-MAX(MAX($D21,$R21),$P$1),DATE(YEAR($D$4)+1,7,0)-MAX(MAX($D21,$R21,$S21),$P$1))
I21:I23I21=VLOOKUP(H21,'Pay Table'!$A$4:$I$25,2,FALSE)*E21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)
K21:K23K21=VLOOKUP(J21,'Pay Table'!$A$4:$I$25,2,FALSE)*F21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)
M21:M23M21=VLOOKUP(L21,'Pay Table'!$A$4:$I$25,9,FALSE)*G21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)
P21:P23P21=SUM(I21:M21)
 
Upvote 0
myall_blues said:
All good - we're getting closer

Something like this maybe? You will no doubt still need to make adjustments based on start dates etc. Note the third lookup formula is different to the first two but that might need an IF.

United101.xlsx
ABCDEFGHIJKLMNOPQRS
17Let (A) = Anniversary date
18Let (B) = award date change
19Let (C) = Start FY, (D) =EOFYLevelsalaryLevelsalaryLevelsalary
20MIN(A,B)-CMAX(A,B)-MIN(A,B)MAX(A,B)-D
21Centre Manager1/09/2024046256HP5.20.00HP5.218511.28HP5.2106109.55124620.831/09/2024
22Practice Manager29/08/20246049256SCH4.314727.02SCH4.312027.07SCH4.464244.4090998.49
23Centre M211/01/202510986165HP5.243863.69HP5.234608.05HP5.268390.92146862.6625/06/2025
Sheet2
Cell Formulas
RangeFormula
E21:E23E21=IF(R21=D21,0,IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),MIN(MAX($D21,$R21),$P$1)-DATE(YEAR($D$4),7,0),MIN(MAX($D21,$R21,$S21),$P$1)-DATE(YEAR($D$4),7,0)))
F21:F23F21=IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),MAX(MAX($D21,$R21),$P$1)-MIN(MAX($D21,$R21),$P$1),MAX(MAX($D21,$R21,S21),$P$1)-MIN(MAX($D21,$R21,S21),$P$1))
G21:G23G21=IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),S21-MAX(MAX($D21,$R21),$P$1),DATE(YEAR($D$4)+1,7,0)-MAX(MAX($D21,$R21,$S21),$P$1))
I21:I23I21=VLOOKUP(H21,'Pay Table'!$A$4:$I$25,2,FALSE)*E21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)
K21:K23K21=VLOOKUP(J21,'Pay Table'!$A$4:$I$25,2,FALSE)*F21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)
M21:M23M21=VLOOKUP(L21,'Pay Table'!$A$4:$I$25,9,FALSE)*G21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)
P21:P23P21=SUM(I21:M21)
Click to expand...
Hi Again

We certainly are getting closer now!! Thank you. :)

Just a couple more tweaks if possible? When I have a start date (Column R) and an end date (Column S), Column F seems to not be including the last day in the calculation? Essentially a day is missing.

Is there a way that the formulas in the various Columns only access Pay Table Column I if the relevant award is HP? So, Column F would be empty unless the Award was HP. That would then mean I would only need to pull down the award in Column L if Column F was empty as the worker was not on the HP award.

Many Thanks

Scott
 
Upvote 0
Can you please explain and give an example of what you mean by
UnitedCloud01 said:
When I have a start date (Column R) and an end date (Column S), Column F seems to not be including the last day in the calculation? Essentially a day is missing.
Click to expand...
I think this fits the second part of the question.

United101.xlsm
ABCDEFGHIJKLMNOPQRS
17Let (A) = Anniversary date
18Let (B) = award date change
19Let (C) = Start FY, (D) =EOFYLevelsalaryLevelsalaryLevelsalary
20MIN(A,B)-CMAX(A,B)-MIN(A,B)MAX(A,B)-D
21Centre Manager1/09/2024046256HP5.20.00HP5.219066.56HP5.2106109.55125176.101/09/2024
22Practice Manager29/08/20246049256SCH4.314727.02SCH4.312027.07SCH4.464244.4090998.49
23Centre M211/01/202510986165HP5.245179.45HP5.235646.18HP5.268390.92149216.5525/06/2025
Sheet2
Cell Formulas
RangeFormula
E21:E23E21=IF(R21=D21,0,IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),MIN(MAX($D21,$R21),$P$1)-DATE(YEAR($D$4),7,0),MIN(MAX($D21,$R21,$S21),$P$1)-DATE(YEAR($D$4),7,0)))
F21:F23F21=IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),MAX(MAX($D21,$R21),$P$1)-MIN(MAX($D21,$R21),$P$1),MAX(MAX($D21,$R21,S21),$P$1)-MIN(MAX($D21,$R21,S21),$P$1))
G21:G23G21=IF(AND(S21<>"",S21<DATE(YEAR($D$4)+1,7,0)),S21-MAX(MAX($D21,$R21),$P$1),DATE(YEAR($D$4)+1,7,0)-MAX(MAX($D21,$R21,$S21),$P$1))
I21:I23I21=LET(vlcolHP,9,vlcolOTH,2,formend,E21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1),IF(LEFT(H21,2)="HP",VLOOKUP(H21,'Pay Table'!$A$4:$I$25,vlcolHP,FALSE)*formend,VLOOKUP(H21,'Pay Table'!$A$4:$I$25,vlcolOTH,FALSE)*formend))
K21:K23K21=LET(vlcolHP,9,vlcolOTH,2,formend,F21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1),IF(LEFT(J21,2)="HP",VLOOKUP(J21,'Pay Table'!$A$4:$I$25,vlcolHP,FALSE)*formend,VLOOKUP(J21,'Pay Table'!$A$4:$I$25,vlcolOTH,FALSE)*formend))
M21:M23M21=LET(vlcolHP,9,vlcolOTH,2,formend,G21/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1),IF(LEFT(L21,2)="HP",VLOOKUP(L21,'Pay Table'!$A$4:$I$25,vlcolHP,FALSE)*formend,VLOOKUP(L21,'Pay Table'!$A$4:$I$25,vlcolOTH,FALSE)*formend))
P21:P23P21=SUM(I21:M21)
 
Upvote 0
You must log in or register to reply here.

Similar threads

HappyChappy
  • Question Question
Hours over basic
Replies
4
Views
402
Fluff
Fluff
E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
89
earth
E
M
  • Question Question
Dynamic Formula
Replies
2
Views
293
Mengli
M
D
  • Question Question
Simplify 2 array formula to calculate risk score
Replies
2
Views
95
djbe17
D
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
930
paulkc
P

Forum statistics

Threads
1,224,743
Messages
6,180,687
Members
452,994
Latest member
Janick

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top