VLOOKUP that will SUM data based on Date Range

UnitedCloud01 · Jun 10, 2024

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

1

Program name

17/10/2024

Award Date Change (QHealth)

2

LEGEND:

Highlighted cells calculate automatically

Surplus/Deficit

1976

Ordinary Hours (38 hours X 52 weeks)

3

*Includes Super

4

Salary and wages positions

01/07/2024

30/06/2025

DO NOT CHANGE

6 - 1660

6 - 1680

6 - 1610

6 - 1690

5

FTE

Position

Anniversary or Commencement (delete Days before if commencing)

# Days before Anniversary

# Days after Anniversary / Commencement

Level

salary

Level

salary

Vehicle

salary sacrifice per FTE

Salary

Name

Start Date

End Date

Contract End Date

Work Type

Super

Workercover

Annual*

LSL Oncosts

Total

Total by Month

Month

Hourly Rate

Monday

Tuesday

Wednesday

Thursday

Friday

Monday

Tuesday

Wednesday

Thursday

Friday

6

Budgeted

Actual Per annum

Choose from drop down list

If vehicle provided, should be same as FTE

$ 6,000.00

11.5%

1.50%

17.50%

1.35%

Hours

FTE

Start

Break

End

Hours

Start

Break

End

Hours

Start

Break

End

Hours

Start

Break

End

Hours

Start

Break

End

Hours

Start

Break

End

Hours

Start

Break

End

Hours

Start

Break

End

Hours

Start

Break

End

Hours

Start

Break

End

Hours

7

1.0

Centre Manager

01/09/2024

HP5.2

HP5.2

Permanent

09:00

00:30

17:00

7.50

09:00

00:30

17:15

7.75

09:00

00:30

17:00

7.50

09:00

00:30

17:15

7.75

09:00

00:30

17:00

7.50

09:00

00:30

17:00

7.50

09:00

00:30

17:15

7.75

09:00

00:30

17:00

7.50

09:00

00:30

17:15

7.75

09:00

00:30

17:00

7.50

8

1.0

Practice Manager

29/08/2024

SCH4.3

SCH4.4

Permanent

09:00

00:30

17:00

7.50

09:00

00:30

17:15

7.75

09:00

00:30

17:00

7.50

09:00

00:30

17:15

7.75

09:00

00:30

17:00

7.50

09:00

00:30

17:00

7.50

09:00

00:30

17:15

7.75

09:00

00:30

17:00

7.50

09:00

00:30

17:15

7.75

09:00

00:30

17:00

9

10

1. Staffing & vehicles

Cell Formulas
Range	Formula
E7:E8	E7	=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:F8	F7	=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:L8	L7	=K7*-6000
M7:M8	M7	=IF(AND(R7="Casual",E7<>"",F7=0),Z71976(B7/365)E7,IF(AND(R7="Casual",E7<>"",F7<>""),Z71976(B7/365)(E7+F7),IF(AND(R7="Casual",E7=0,F7<>""),Z71976(B7/365)*F7,H7+J7+L7)))
S7:S8	S7	=IF(R7="Casual",(M7)$S$6,((M7)+(M7)/524$U$6)$S$6)
T7:T8	T7	=(M7+((M7)$S$6))$T$6
U7:U8	U7	=(((M7)/524)$U$6)
V7:V8	V7	=((M7)+((M7)$S$6))$V$6
W7:W8	W7	=IF(R7="Casual",SUM(M7,S7:T7,V7),SUM(M7,S7:V7))
X7:X8	X7	=IF(P7<>"",((W7/(DATEDIF($D$4,P7,"M"))Y7)),(W7/12)Y7)
Z7:Z8	Z7	=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:AA8	AA7	=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:AB8	AB7	=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:B8	B7	=AB7
J7:J8	J7	=IF(D7="",0,IF(R7="Casual",0,IF(TODAY()<$M$1,(IFERROR((VLOOKUP(I7,'Pay Table'!$A$3:$B$83,2,FALSE)),0))B7/365F7,(IFERROR((VLOOKUP(I7,'Pay Table'!$H$3:$I$83,2,FALSE)),0))B7/365F7)))
AF7:AF8,BP7:BP8,BL7:BL8,BH7:BH8,BD7:BD8,AZ7:AZ8,AV7:AV8,AR7:AR8,AN7:AN8,AJ7:AJ8	AF7	=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,H9	A9	=SUM(A7:A8)
C9	C9	=(A9-B9)
H7:H8	H7	=IF(D7="",0,IF(R7="Casual",0,IF(TODAY()<$M$1,(IFERROR((VLOOKUP(G7,'Pay Table'!$A$3:$B$83,2,FALSE)),0))B7/365E7,(IFERROR((VLOOKUP(G7,'Pay Table'!$H$3:$I$83,2,FALSE)),0))B7/365E7)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C9	Cell Value	>0	text	NO
C9	Cell Value	=0	text	NO
C9	Cell Value	<0	text	NO
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:BO9	Cell Value	<>""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G7:G8	List	='Pay Table'!$A$85:$A$161
I7:I8	List	='Pay Table'!$A$85:$A$161
K7:K8	Any value
Y7:Y8	List	1,2,3,4,5,6,7,8,9,10,11,12
R7:R8	List	Permanent, Max Term, Casual

BUD - Mini Sheet.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

PAY TABLES

2

Level

24/25 Budget Salary

24/25 Weekly Rate

24/25 F/ntly Rate

24/25 Hourly Rate

24/25 Hourly Casual Rate

24/25 Budget Salary

24/25 Weekly Rate

24/25 F/ntly Rate

24/25 Hourly Rate

24/25 Hourly Casual Rate

23/24 Budget Salary

3

SCHADS AWARD

3.75%

Increase from 23/24

1.25

SCHADS AWARD

4

SCH1.1

SCH1.1

$ 48,396

5

SCH1.2

SCH1.2

$ 49,956

6

SCH1.3

SCH1.3

$ 51,740

7

SCH2.1

SCH2.1

$ 63,647

8

SCH2.2

SCH2.2

$ 65,643

9

SCH2.3

SCH2.3

$ 67,638

10

SCH2.4

SCH2.4

$ 69,437

11

SCH3.1

SCH3.1

$ 71,136

12

SCH3.2

SCH3.2

$ 73,171

13

SCH3.3

SCH3.3

$ 74,752

14

SCH3.4

SCH3.4

$ 76,274

15

SCH4.1

SCH4.1

$ 82,044

16

SCH4.2

SCH4.2

$ 84,197

17

SCH4.3

SCH4.3

$ 86,351

18

SCH4.4

SCH4.4

$ 88,288

68

QHEALTH HP

17/10/2023

QHEALTH HP

17/10/2024

78

HP4.1

$ 124,435

$ 4,769.60

$ 62.76

$ 77.19

HP4.1

$ 128,169

$ 4,912.70

$ 64.64

$ 79.51

79

HP4.2

$ 127,031

$ 4,869.10

$ 64.07

$ 78.80

HP4.2

$ 130,843

$ 5,015.20

$ 65.99

$ 81.17

80

HP4.3

$ 130,350

$ 4,996.30

$ 65.74

$ 80.86

HP4.3

$ 134,261

$ 5,146.20

$ 67.71

$ 83.29

81

HP4.4

$ 133,901

$ 5,132.40

$ 67.53

$ 83.06

HP4.4

$ 137,918

$ 5,286.40

$ 69.56

$ 85.56

82

HP5.1

$ 140,767

$ 5,395.60

$ 70.99

$ 87.32

HP5.1

$ 144,991

$ 5,557.50

$ 73.13

$ 89.94

83

HP5.2

$ 146,883

$ 5,630.00

$ 74.08

$ 91.12

HP5.2

$ 151,289

$ 5,798.90

$ 76.30

$ 93.85

Pay Table

Cell Formulas
Range	Formula
B4:B18	B4	=O4*(1+$B$3)
C4:C18,J4:J18	C4	=E4*38
D4:D18,K4:K18	D4	=E4*76
E4:E18,L4:L18	E4	=B4/1976
F4:F18,M4:M18	F4	=E4*$F$3
I4:I18	I4	=O4*(1+$B$3)

Thanks so much for considering helping me.

Scott

UnitedCloud01 · Jun 16, 2024

myall_blues said:
Can you please explain and give an example of what you mean by

I think this fits the second part of the question.

United101.xlsm
A B C D E F G H I J K L M N O P Q R S
17 Let (A) = Anniversary date
18 Let (B) = award date change
19 Let (C) = Start FY, (D) =EOFY Level salary Level salary Level salary
20 MIN(A,B)-C MAX(A,B)-MIN(A,B) MAX(A,B)-D
21 Centre Manager 1/09/2024 0 46 256 HP5.2 0.00 HP5.2 19066.56 HP5.2 106109.55 125176.10 1/09/2024
22 Practice Manager 29/08/2024 60 49 256 SCH4.3 14727.02 SCH4.3 12027.07 SCH4.4 64244.40 90998.49
23 Centre M2 11/01/2025 109 86 165 HP5.2 45179.45 HP5.2 35646.18 HP5.2 68390.92 149216.55 25/06/2025
Sheet2
Cell Formulas
Range Formula
E21:E23 E21 =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:F23 F21 =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:G23 G21 =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:I23 I21 =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:K23 K21 =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:M23 M21 =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:P23 P21 =SUM(I21:M21)

Hi Again

Apologies for the late reply, I didn't do any work over the weekend...I needed time out. :P

In relation to the start and end date, I have noticed that there seems to be a day missing from the calculation. For example, if the start date is the 28/07/2024 and the end date is the 06/01/2025, then there is 0 days in column E, 81 days in column F (there are 82 if you include the last day in the calculation), and 81 days in Column G. If you calculate the days between 28/07/2024 and 06/01/2025 there are 163 days. The spreadsheet has 162 days. Hope that makes sense.

In relation to the amendment of the formulas to exclude non-HP award from calculations, the change now no longer seems to pick up the increase in the award post the date in P1 like the previous formula did. Also, the columns for non-HP award still have data, that is, Column K and M contain salary data instead of just one column which, for those awards, would be sufficient as they aren't subject to the award date change.

I think I might stick with the previous formulas you provided to me. They do the job brilliantly. I have changed the column titles so they will make sense to me going forward. It is easy enough to make sure the non-HP award levels are input into each column. You have spent so much time helping me as it is!!

This is how it will look:

BUD - Mini Sheet.xlsx

D

E

F

G

H

I

J

K

L

M

N

O

P

R

S

11

01/07/2024

30/06/2025

DO NOT CHANGE

12

Anniversary or Commencement

# Days before Anniversary / Commencement

# Days after Anniversary / Commencement

Level

salary

Level

salary

Level

salary

Vehicle

salary sacrifice per FTE

Salary

Start Date

End Date

13

# Days before Award Change*

# Days after Award Change*

Choose from drop down list

If vehicle provided, should be same as FTE

$ 6,000.00

14

28/07/2024

HP5.1

HP5.2

HP5.2

28/07/2024

06/01/2025

15

29/08/2024

SCH4.3

SCH4.4

SCH4.4

27/05/2025

16

* if applicable

1. Staffing & vehicles

Cell Formulas
Range	Formula
E14:E15	E14	=IF(R14=D14,0,IF(AND(S14<>"",S14<DATE(YEAR($D$4)+1,7,0)),MIN(MAX($D14,$R14),$P$1)-DATE(YEAR($D$4),7,0),MIN(MAX($D14,$R14,$S14),$P$1)-DATE(YEAR($D$4),7,0)))
F14:F15	F14	=IF(AND(S14<>"",S14<DATE(YEAR($D$4)+1,7,0)),MAX(MAX($D14,$R14),$P$1)-MIN(MAX($D14,$R14),$P$1),MAX(MAX($D14,$R14,$S14),$P$1)-MIN(MAX($D14,$R14,S14),$P$1))
G14:G15	G14	=IF(AND(S14<>"",S14<DATE(YEAR($D$4)+1,7,0)),S14-MAX(MAX($D14,$R14),$P$1),DATE(YEAR($D$4)+1,7,0)-MAX(MAX($D14,$R14,$S14),$P$1))
O14:O15	O14	=N14*-6000
P14:P15	P14	=SUM(I14:M14)
M14:M15	M14	=VLOOKUP(L14,'Pay Table'!$A$3:$I$83,9,FALSE)*G14/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)
I14:I15	I14	=VLOOKUP(H14,'Pay Table'!$A$3:$I$83,2,FALSE)*E14/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)
I16,M16:P16,K16	I16	=SUM(I14:I15)
K14:K15	K14	=VLOOKUP(J14,'Pay Table'!$A$3:$I$83,2,FALSE)*F14/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)

Cells with Data Validation
Cell	Allow	Criteria
N14:N15	Any value
H14:H15	List	='Pay Table'!$A$85:$A$161
J14:J15	List	='Pay Table'!$A$85:$A$161
L14:L15	List	='Pay Table'!$A$85:$A$161

Thanks so much for all your help!!

Chat soon

Scott

UnitedCloud01 · Jun 17, 2024

myall_blues said:
Can you please explain and give an example of what you mean by

I think this fits the second part of the question.

United101.xlsm
A B C D E F G H I J K L M N O P Q R S
17 Let (A) = Anniversary date
18 Let (B) = award date change
19 Let (C) = Start FY, (D) =EOFY Level salary Level salary Level salary
20 MIN(A,B)-C MAX(A,B)-MIN(A,B) MAX(A,B)-D
21 Centre Manager 1/09/2024 0 46 256 HP5.2 0.00 HP5.2 19066.56 HP5.2 106109.55 125176.10 1/09/2024
22 Practice Manager 29/08/2024 60 49 256 SCH4.3 14727.02 SCH4.3 12027.07 SCH4.4 64244.40 90998.49
23 Centre M2 11/01/2025 109 86 165 HP5.2 45179.45 HP5.2 35646.18 HP5.2 68390.92 149216.55 25/06/2025
Sheet2
Cell Formulas
Range Formula
E21:E23 E21 =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:F23 F21 =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:G23 G21 =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:I23 I21 =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:K23 K21 =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:M23 M21 =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:P23 P21 =SUM(I21:M21)

Me again

I have encountered an issue when there is a casual worker. Would you mind considering any suggestions to improve the formula in Column P as well as E, F, and G, given the dates entered into Column S? Thanks again.

BUD - 47T - Core - FY 24-25 v3 - Scott - PND.xlsx

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

14

0.4

Practice Administration Worker

31/10/2024

SCH2.1

30/08/2024

Casual

1. Staffing & vehicles

Cell Formulas
Range	Formula
B14	B14	=AE14
E14	E14	=IF($D14="","",IF($R14=$D14,0,IF(AND($S14<>"",$S14<DATE(YEAR($D$4)+1,7,0)),MIN(MAX($D14,$R14),$P$1)-DATE(YEAR($D$4),7,0),MIN(MAX($D14,$R14,$S14),$P$1)-DATE(YEAR($D$4),7,0))))
F14	F14	=IF($D14="","",IF(AND($S14<>"",$S14<DATE(YEAR($D$4)+1,7,0)),MAX(MAX($D14,$R14),$P$1)-MIN(MAX($D14,$R14),$P$1),MAX(MAX($D14,$R14,$S14),$P$1)-MIN(MAX($D14,$R14,$S14),$P$1)))
G14	G14	=IF($D14="","",IF(AND($S14<>"",$S14<DATE(YEAR($D$4)+1,7,0)),$S14-MAX(MAX($D14,$R14),$P$1),DATE(YEAR($D$4)+1,7,0)-MAX(MAX($D14,$R14,$S14),$P$1)))
I14	I14	=IF($D14="","",IF($U14="Casual",0,VLOOKUP($H14,'Pay Table'!$A$3:$I$83,2,FALSE)*$E14/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)))
K14	K14	=IF($D14="","",IF($U14="Casual",0,VLOOKUP($J14,'Pay Table'!$A$3:$I$83,2,FALSE)*$F14/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)))
L14	L14	=IF(J14="","",J14)
M14	M14	=IF($D14="","",IF($U14="Casual",0,VLOOKUP($L14,'Pay Table'!$A$3:$I$83,9,FALSE)*$G14/(DATE(YEAR($D$4)+1,6,30)-DATE(YEAR($D$4),7,1)+1)))
O14	O14	=N14*-6000
P14	P14	=IF(AND(U14="Casual",E14<>"",G14=0),AC141976(B14/365)E14,IF(AND(U14="Casual",E14<>"",G14<>""),AC141976(B14/365)(E14+G14),IF(AND(U14="Casual",E14=0,G14<>""),AC141976(B14/365)*G14,SUM(I14:M14))))

myall_blues · Jun 17, 2024

You're probably going to need to set out in a table or list for me the clear rules about how all these different dates work. In the formulas I provided I didn't account for all your original conditions because there was insufficient data to work out what they were supposed to be doing. For instance, in the example you just provided, the end date in column S is two months prior to the anniversary/commencement date in column D, so I'm not surprised that it shows a -62, which is the backwards days between the end date and the start date.

UnitedCloud01 · Jun 17, 2024

Hey

myall_blues said:
You're probably going to need to set out in a table or list for me the clear rules about how all these different dates work. In the formulas I provided I didn't account for all your original conditions because there was insufficient data to work out what they were supposed to be doing. For instance, in the example you just provided, the end date in column S is two months prior to the anniversary/commencement date in column D, so I'm not surprised that it shows a -62, which is the backwards days between the end date and the start date.

Hi Again.

Are you sure you don't mind?

I can set it out with more detail than what I initially provided. Apologies.

My original mixed bag of tricks (formulas) countered for most everything I could think of. The addition of the during financial year award change stuffed me and was beyond my self taught ability...arghhh.

I appreciate you persisting with me.

Will send through a more detailed description first thing in morning.

Chat soon

Scott

UnitedCloud01 · Jun 18, 2024

myall_blues said:
You're probably going to need to set out in a table or list for me the clear rules about how all these different dates work. In the formulas I provided I didn't account for all your original conditions because there was insufficient data to work out what they were supposed to be doing. For instance, in the example you just provided, the end date in column S is two months prior to the anniversary/commencement date in column D, so I'm not surprised that it shows a -62, which is the backwards days between the end date and the start date.

Hi Again

This took longer than I thought.

I hope this explains what I am doing more thoroughly. I will explain what my current formulas do in the format I originally sent to you.

Column E (# Days before Anniversary)

If Column D (Anniversary or Commencement) is empty, then Column E (# Days before Anniversary) is empty.
If Column D (Anniversary or Commencement) is empty and Column O (Start Date) is empty, then Column E (# Days before Anniversary) is empty.
If Column O (Start Date) is the same as Column D (Anniversary or Commencement), then Column E (# Days before Anniversary) is zero days.
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is empty, and Column P (End Date) is not empty and Column P (End Date) is less than the date in Column D (Anniversary or Commencement), then the amount of days when D4 (the beginning of the financial year) subtracted from Column P (End Date) Plus 1 day is generated and is used to calculate Column H (salary).
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is not empty, and Column P (End Date) is empty and Column O (Start Date) is less than the date in Column D (Anniversary or Commencement), then the amount of days when Column D (Anniversary or Commencement) subtracted from Column O (Start Date) Plus 1 day is generated and is used to calculate Column H (salary).
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is not empty, and Column P (End Date) is not empty and Column O (Start Date) is less than the Column D (Anniversary or Commencement) and Column P (End Date) is greater than the Column D (Anniversary or Commencement), then the amount of days when Column O (Start Date) subtracted from Column D (Anniversary or Commencement date) is generated and is used to calculate Column H (salary).
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is not empty, and Column P (End Date) is not empty and Column O (Start Date) is less than the Column D (Anniversary or Commencement) and Column P (End Date) is less than the Column D (Anniversary or Commencement), then the amount of days when Column O (Start Date) is subtracted from Column P (End Date) is generated and is used to calculate Column H (salary).
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is empty, and Column D (Anniversary or Commencement) is the same as D4 (beginning of the financial year) then the amount of days when D4 (beginning of the financial year) is subtracted from Column D (Anniversary or Commencement) is generated and is used to calculate Column H (salary). Where Column D (Anniversary or Commencement) is different from D4 (beginning of the financial year) then the number of days when D4 (beginning of the financial year) is subtracted from Column D (Anniversary or Commencement) PLUS 1 day is generated and is used to calculate Column H (salary).

NOTE: If the date in Column D (Anniversary or Commencement) does not equal a date in Column O (Start Date) because the latter is empty, it means that the date entered is the anniversary date for when the worker commenced. In this instance, the date is then used to calculate the days between the start of the financial year on July 01st until this date. Column H uses this number to calculate the salary against the workers current award level before they increase to the next pay point after this date which is captured in Column J (salary).

NOTE: If the date in Column D (Anniversary or Commencement) does equal a date in Column O (Start Date), it means that the date entered is the commencement date. In this instance, the date will become a future anniversary date and returns a 0 as there were no days between the start of the financial year until this date. Column I will be empty as no salary is calculated as Column J (salary) will alone be used to calculate the worker’s salary.

Column F (# Days after Anniversary / Commencement)

If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is not empty, and Column P (End Date) is not empty, and Column O (Start Date) is less than Column D (Anniversary or Commencement) and Column P (End Date) is less than Column D (Anniversary or Commencement) then Column F (# Days after Anniversary / Commencement) is zero days.
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is not empty and Column P (End Date) is not empty and Column O (Start Date) is less than Column D (Anniversary or Commencement) and Column P (End Date) is less than E4 (the end of the financial year), then the amount of days when Column D (Anniversary or Commencement) is subtracted from Column P (End Date) Plus 1 day is generated and is used to calculate Column J (salary)
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is not empty and Column P (End Date) is not empty and Column O (Start Date) is the same as Column D (Anniversary or Commencement) and Column P (End Date) is less than E4 (the end of the financial year), then the amount of days when Column D (Anniversary or Commencement) is subtracted from Column P (End Date) Plus 1 day is generated and is used to calculate Column J (salary)
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is not empty and Column O (Start Date) is less than Column D (Anniversary or Commencement), then the number of days when Column D (Anniversary or Commencement) is subtracted from E4 (the end of the financial year) is generated and is used to calculate Column J (salary)
If Column D (Anniversary or Commencement) is not empty and Column O (Start Date) is not empty and Column O (Start Date) is the same as Column D (Anniversary or Commencement), then the number of days when Column D (Anniversary or Commencement) is subtracted from E4 (the end of the financial year) Plus 1 day is generated and is used to calculate Column J (salary)
If Column D (Anniversary or Commencement) is empty and Column P (End Date) is empty, then Column F (# Days after Anniversary / Commencement) is empty
If Column D (Anniversary or Commencement) is not empty and Column P (End Date) is empty and Column E (# Days before Anniversary) is not Zero days, then the number of days when Column D (Anniversary or Commencement) is subtracted from E4 (the end of the financial year) is generated and is used to calculate Column J (salary)
If Column D (Anniversary or Commencement) is not empty and Column P (End Date) is empty and Column E (# Days before Anniversary) is empty, then the number of days when Column D (Anniversary or Commencement) is subtracted from E4 (the end of the financial year) Plus 1 day is generated and is used to calculate Column J (salary)
If Column D (Anniversary or Commencement) is not empty and Column P (End Date) is not empty, and Column P (End Date) is less than or equal to Column D (Anniversary or Commencement), then Column F (# Days after Anniversary / Commencement) is zero days. Where Column P (End Date) is not less than or equal to Column D (Anniversary or Commencement), then the number of days when Column D (Anniversary or Commencement) is subtracted from Column P (End Date) is generated and is used to calculate Column J (salary).

Column G (Level)

The award level and pay point here is used with the number of days before anniversary to determine the worker’s salary for that part of the financial year. Where Column E (# Days before Anniversary) is zero days, no award needs to be chosen.

Column H (salary)

If Column R (Work Type) is Casual, the salary is not calculated and is reported as a Zero amount. This is where I attempted to use a VLOOKUP range that varied by date of the Award change, but this just meant that the salaries changed after this date was reached and did not capture that part of the year (pre award date change) would still be the previously award level.

Column I (Level)

As per Column G.

Column J (salary)

As per Column H

Column M (Salary)

If Column R (Work Type) is Casual and Column E (# Days before Anniversary) is not empty and Column F (# Days after Anniversary / Commencement) is zero days, then Column M (Salary) is calculated by multiplying Column Z (Hourly Rate) with 1976 (38 hours X 52 weeks) multiplied by Column B (Full Time Equivalent status of worker where 1.0 is full time) divided by 365 (days in year) multiplied by the number of days in Column E (# Days before Anniversary).
If Column R (Work Type) is Casual and Column E (# Days before Anniversary) is not empty and Column F (# Days after Anniversary / Commencement) is not empty, then Column M (Salary) is calculated by multiplying Column Z (Hourly Rate) with 1976 (38 hours X 52 weeks) multiplied by Column B (Full Time Equivalent status of worker where 1.0 is full time) divided by 365 (days in year) multiplied the addition of the number of days in Column E (# Days before Anniversary) and Column F (# Days after Anniversary / Commencement).
If Column R (Work Type) is Casual and Column E (# Days before Anniversary) is zero days and Column F (# Days after Anniversary / Commencement) is not empty, then Column M (Salary) is calculated by multiplying Column Z (Hourly Rate) with 1976 (38 hours X 52 weeks) multiplied by Column B (Full Time Equivalent status of worker where 1.0 is full time) divided by 365 (days in year) multiplied by the number of days in Column F (# Days after Anniversary / Commencement).
Where Column R (Work Type) is not Casual, then Column M (Salary) is the sum of Column H (salary), Column J (salary), and Column L (salary sacrifice).

Column O (Start Date)

When a worker has commenced work during the financial year, the date of commencement is entered. This will be the future years’ anniversary date.

Column P (End Date)

When I worker has ceased work during the financial year, the date of their termination is entered. This date supersedes the E4 (the end of the financial year) date in Column H and Column J (salary) calculations.

Column Q (Contract End Date)

This is for my reference only and does not factor in any formulas.

Column R (Work Type)

This tells me the nature of the Worker’s employment.

Column S (Super)

This calculates the worker’s superannuation component of their salary. The calculations vary whether the Worker is Casual or not.

Column T (Workcover)

This calculates the worker’s Workcover costs as a part of their salary. Column R (Work Type) does not factor here.

Column U (Annual Leave)

This calculates the workers Annual Leave costs with a superannuation component.

Column V (LSL Oncosts)

This calculates the long service leave entitlement for the worker.

Column W (Total)

This is the total salary and oncosts for the worker. Column R (Work Type) does impact on this calculation.

Column Z (Hourly Rate)

Column R (Work Type) does determine the VLOOKUP, that is Casual vs not. Furthermore, within this bifurcation, I again attempted to use a VLOOKUP range that varied by date of the Award change, but this again just meant that the hourly rate changed after this date was reached and did not capture that part of the year (pre award date change) would still be the previously award level.

I know this is a lot of information. I hope that it is what you wanted.

Chat soon

Scott

myall_blues · Jun 20, 2024

Hi Scott

I'll have a look at this but it might take me a while. Before I do a question about your conditions - take for example the first three:

UnitedCloud01 said:
Column E (# Days before Anniversary)

If Column D (Anniversary or Commencement) is empty, then Column E (# Days before Anniversary) is empty.

If Column D (Anniversary or Commencement) is empty and Column O (Start Date) is empty, then Column E (# Days before Anniversary) is empty.

If Column O (Start Date) is the same as Column D (Anniversary or Commencement), then Column E (# Days before Anniversary) is zero days.

Since column E is going to contain a formula a return value of zero will always be zero unless it is forced to empty. By this I mean for example if I have the formula

Excel Formula:

=A1-B1

and it evaluates to zero, then the only way to make it 'empty' is to add:

Excel Formula:

=IF(A1-B1=0,"",A1-B1)

It's more aesthetically pleasing but complicates the formulas and has no practical value. Do you need them forced to zero?

Also the number of conditions here suggests that some VBA code might be the way to go, likely as a user defined function to go through all the variants. Would that be OK?

myall_blues · Jun 20, 2024

myall_blues said:
It's more aesthetically pleasing but complicates the formulas and has no practical value. Do you need them forced to zero?

I meant do you need them forced to empty.

UnitedCloud01 · Jun 20, 2024

myall_blues said:
Hi Scott

I'll have a look at this but it might take me a while. Before I do a question about your conditions - take for example the first three:

Since column E is going to contain a formula a return value of zero will always be zero unless it is forced to empty. By this I mean for example if I have the formula

Excel Formula:

=A1-B1

and it evaluates to zero, then the only way to make it 'empty' is to add:

Excel Formula:

=IF(A1-B1=0,"",A1-B1)

It's more aesthetically pleasing but complicates the formulas and has no practical value. Do you need them forced to zero?

Also the number of conditions here suggests that some VBA code might be the way to go, likely as a user defined function to go through all the variants. Would that be OK?

Hi Murray

Thanks so much for the reply. I guess the reason was so if other people need to look at the Budget it would be more obvious to them that if there was a Zero it meant that there were Zero days being used in the calculation. At the clinic, I have trained everyone to view anything empty, in an otherwise completed table, document, assessment, spreadsheet row etc., as a potential error of omission and that, as much as possible, there needs to something to acknowledge that the data is deliberate and not an accidental omission. For the purposes of this, however, I can just make a note of what your solution is.

If you need to use the VBA code, please do what you need. I am just so appreciative of the assistance!

Many thanks

Scott

UnitedCloud01 · Jun 20, 2024

myall_blues said:
I meant do you need them forced to empty.

Gotcha. I did force to empty so that I didn't have error codes etc popping up if I was inputting data in an ad hoc way and formulas in cells did not have all the data required to generate a result. It was an aesthetic solution more than anything. Also, again, if other people were using the data, I didn't want them to be confused. I have shared this budget spreadsheet with other sites to help them budget better.

VLOOKUP that will SUM data based on Date Range

UnitedCloud01

New Member

UnitedCloud01

New Member

Excel Facts

UnitedCloud01

New Member

myall_blues

Well-known Member

UnitedCloud01

New Member

UnitedCloud01

New Member

myall_blues

Well-known Member

myall_blues

Well-known Member

UnitedCloud01

New Member

UnitedCloud01

New Member

Similar threads

Forum statistics

Share this page

Cell Formulas
Range	Formula
E21:E23	E21	=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:F23	F21	=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:G23	G21	=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:I23	I21	=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:K23	K21	=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:M23	M21	=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:P23	P21	=SUM(I21:M21)

VLOOKUP that will SUM data based on Date Range

New Member

New Member

Excel Facts

New Member

Well-known Member

New Member

New Member

Well-known Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock