IF DATE STATEMENT

[ChetanPuri]

Hi All Excel Wizards,

I am trying to create an if Statement in column S based on anniversary date in column H. I am trying to have a statement that if Anniversary date is =<01/01/2024, then divide the next level salary by 365 Days and then multiply by no. of days (1/01/2024-Anniversary date), however if Anniversary date is => 01/01/2024 then pick the values from next column, but I get False if anniversary date is more than 01 Jan 2024. Any help will be appreciated.

Employee positions report_31 March 2023.xlsx
	S
2	39,386.14
Raw Data to flow in worksheets

Cell Formulas
Range	Formula
S2	S2	=IF(H2<=Summary!$B$2,'Raw Data to flow in worksheets'!P2/365*(Summary!$B$2-'Raw Data to flow in worksheets'!H2))

 

[RudRud]

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1							Anniv Date								Salary				Next Column
2	1/1/24						12/23/23								35000			863.013699	123
3							1/1/24								35000			8778	8778
4							1/2/24								35000			123	123
Sheet1

Cell Formulas
Range	Formula
S2	S2	=IF(H2<$B$2,P2/365*DATEDIF(H2,$B$2,"d"),IF(H2>=$B$2,P2))
S3:S4	S3	=IF(H3<$B$2,P3/365*DATEDIF(H3,$B$2,"d"),IF(H3>=$B$2,T3))

something like this? if false pick which column?

 

[ChetanPuri]

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1							Anniv Date								Salary				Next Column
2	1/1/24						12/23/23								35000			863.013699	123
3							1/1/24								35000			8778	8778
4							1/2/24								35000			123	123
Sheet1

Cell Formulas
Range	Formula
S2	S2	=IF(H2<$B$2,P2/365*DATEDIF(H2,$B$2,"d"),IF(H2>=$B$2,P2))
S3:S4	S3	=IF(H3<$B$2,P3/365*DATEDIF(H3,$B$2,"d"),IF(H3>=$B$2,T3))

something like this? if false pick which column?

Perfect thank you much appreciated

 

[ChetanPuri]

Hi Rudd,
Sorry to bother you is there a formula which I tried to create in Column S Row 34. I am trying to do is if Date in Colum H is less than 01 January 2024 then subtract 01/07/2023 and divide the amount in column M/365 and multiply it by number of days i.e 01/01/2024-01/07/2023, otherwise, if Greater than 01 Jan 2024 then subtract it from 30 June 2024- date in Column H and then divide amount in column R * times by no. of days. Thank you for all your help

Salaries Budget 23-24 - 30 April 2023.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
1		1/07/2023	1/01/2024	30/06/2024
2	Employee Code	Surname	Given	Position Number	Position Title	Start Date (with org)	Contract Expiry Date	Anniversary Date	Employee Type	Hours/Fortnight (as at EOM, inc flexible arrangements etc)	Classification	Step	Current Salary	Division Description	Next Step	Next Level Salary - Full	Next Salary Level - Incudes P-T Calculation	As Per New EBA	July to 31 December 2023	Anniversry date to NEW EBA	EBA to End of Financial Year	Total Salary	SUPER	A/L Loading	Work Cover	Salary with Oncost
3	303			173	Health Workforce Consultant	4-Jan-22		4-Jan-24	Secondment	76.00	L6	1	99,357.00	Health Workforce	2	105,290.00	105,290.00	107,395.80	50,086.82	882.71	52,373.84	103,343.36	11,367.77	417.35	2,066.87	#########
4	316			21	Executive Coordinator	3-May-22		3-May-24	Permanent	76.00	L4	1	77,112.00	Health Workforce	2	83,045.00	83,045.00	84,705.90	38,872.90	28,544.73	13,460.12	80,877.74	8,896.55	326.62	1,617.55	91,718.47
5	97			173	Health Workforce Consultant	31-Aug-17		31-Aug-23	Permanent	60.80	L6	3	109,305.00	Health Workforce	3	109,305.00	66,457.44	67,786.59	#VALUE!	36,834.29	33,614.72	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
6	350			178	Health Workforce Support Officer	8-Sep-22		8-Sep-23	Permanent	76.00	L4	1	77,112.00	Health Workforce	2	83,045.00	83,045.00	84,705.90	14,366.07	26,164.86	42,004.84	82,535.78	9,078.94	333.32	1,650.72	93,598.75
7	207			280	Health Workforce Development Manager	23-Apr-19		23-Apr-24	Permanent	76.00	L8	1	125,793.00	Health Workforce	2	130,360.00	130,360.00	132,967.20	102,012.95	41,165.19	24,771.97	167,950.11	18,474.51	678.26	3,359.00	#########
8	332			44	Health Workforce Programs Manager	8-Aug-22		8-Aug-23	Permanent	76.00	L8	3	134,927.00	Health Workforce	3	134,927.00	134,927.00	137,625.54	13,677.53	53,970.80	68,247.19	135,895.52	14,948.51	548.81	2,717.91	#########
9	179			48	Health Workforce Consultant	8-Oct-18		8-Oct-23	Permanent	76.00	L6	2	105,290.00	Health Workforce	3	109,305.00	109,305.00	111,491.10	28,269.64	25,454.59	55,287.37	109,011.60	11,991.28	440.24	2,180.23	#########
10	339			320	Health Workforce Support Officer	29-Aug-22		29-Aug-23	Permanent	76.00	L4	1	77,112.00	Health Workforce	2	83,045.00	83,045.00	84,705.90	12,253.41	28,440.07	42,004.84	82,698.33	9,096.82	333.97	1,653.97	93,783.08
11	346			61	Health Workforce Support Officer	5-Sep-22		5-Sep-23	Permanent	76.00	L4	1	77,112.00	Health Workforce	2	83,045.00	83,045.00	84,705.90	13,732.27	26,847.42	42,004.84	82,584.54	9,084.30	333.51	1,651.69	93,654.05
12	296			264	Health Workforce Policy and Strategy Lead	#########		#########	Permanent	76.00	L7	2	117,152.00	Health Workforce	3	121,830.00	121,830.00	124,266.60	44,614.05	14,686.36	61,622.62	120,923.02	13,301.53	488.34	2,418.46	#########
13	217			296	Health Workforce Coordinator - Locum	29-Jul-19		29-Jul-23	Permanent	76.00	L5	1	87,493.00	Health Workforce	2	93,426.00	93,426.00	95,294.52	6,472.08	39,930.02	47,255.64	93,657.74	10,302.35	378.23	1,873.15	#########
14	314			247	Health Workforce Senior Coordinator - Outreach	7-Mar-22		7-Mar-24	Permanent	76.00	L6	1	99,357.00	Health Workforce	2	105,290.00	105,290.00	107,395.80	67,780.53	19,419.51	33,837.03	121,037.08	13,314.08	488.80	2,420.74	#########
15	330			60	Health Workforce Consultant	20-Jul-22		20-Jul-23	Permanent	76.00	L6	1	99,357.00	Health Workforce	2	105,290.00	105,290.00	107,395.80	4,899.80	47,596.85	53,256.55	105,753.19	11,632.85	427.08	2,115.06	#########
16	41			248	Health Workforce Coordinator - Education	18-Sep-06		18-Sep-23	Permanent	60.80	L5	3	96,036.00	Health Workforce	3	96,036.00	58,389.89	59,557.69	20,522.76	27,626.79	29,534.09	77,683.64	8,545.20	392.15	1,553.67	88,174.67
17	277			162	Health Workforce Education Team Lead	#########		#########	Permanent	76.00	L7	1	111,221.00	Health Workforce	2	117,152.00	117,152.00	119,495.04	43,878.97	12,517.61	59,256.44	115,653.03	12,721.83	467.06	2,313.06	#########
18	331			319	Health Workforce Coordinator - Policy and Strategy	8-Aug-22		8-Aug-23	Permanent	76.00	L5	1	87,493.00	Health Workforce	2	93,426.00	93,426.00	95,294.52	8,869.15	37,370.40	47,255.64	93,495.19	10,284.47	377.58	1,869.90	#########
19	220			59	Health Workforce Access Team Lead	19-Aug-19		19-Aug-23	Permanent	76.00	L7	3	121,830.00	Health Workforce	3	121,830.00	121,830.00	124,266.60	16,021.48	45,060.41	61,622.62	122,704.51	13,497.50	495.54	2,454.09	#########
20	224			47	Health Workforce Engagement Team Lead	23-Sep-19		23-Sep-23	Permanent	76.00	L7	2	117,152.00	Health Workforce	3	121,830.00	121,830.00	124,266.60	26,640.04	33,378.08	61,622.62	121,640.74	13,380.48	491.24	2,432.81	#########
21	364			323	Workforce Planning Consultant	#########		#########	Permanent	76.00	L6	1	99,357.00	Health Workforce	2	105,290.00	105,290.00	107,395.80	38,653.96	11,827.10	53,256.55	103,737.60	11,411.14	418.94	2,074.75	#########
22	230			50	Future Health Workforce Consultant	8-Oct-19		8-Oct-23	Permanent	76.00	L6	2	105,290.00	Health Workforce	3	109,305.00	109,305.00	111,491.10	28,269.64	25,454.59	55,287.37	109,011.60	11,991.28	440.24	2,180.23	#########
23	66			52	Health Workforce Coordinator	19-Jul-99		19-Jul-23	Permanent	55.60	L5	3	96,036.00	Health Workforce	3	96,036.00	53,396.02	54,463.94	4,472.91	43,676.65	27,008.14	75,157.70	8,267.35	414.89	1,503.15	85,343.09
Sheet2

 

[RudRud]

Hi Sorry for late reply, I'm still trying to understand the conditions that you create.

CASE1 Date in Column H < 01/01/2024

Formula 1: (Column H date - subtract 01/07/2023) / (amount column M / 365) * (ex 01/01/2024 - 01/07/2023)

Formula 2: (amount column M / 365) / (Column H date - subtract 01/07/2023) * (ex 01/01/2024 - 01/07/2023)

Case2 Column H >= 01/01/2024

Formula 1: substract 30 june 2024 - date in column H / amount column R * (01/01/2024- column H date)

Formula 2: amount column R / substract 30 june 2024 - date in column H * (01/01/2024- column H date)

Formula 1 or formula 2 these two which one is correct? or can you give the expected result that you're looking for?

Book1
	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
2	Employee Code	Surname	Given	Position Number	Position Title	Start Date (with org)	Contract Expiry Date	Anniversary Date	Employee Type	Hours/Fortnight (as at EOM, inc flexible arrangements etc)	Classification	Step	Current Salary	Division Description	Next Step	Next Level Salary - Full	Next Salary Level - Incudes P-T Calculation	As Per New EBA	July to 31 December 2023	Anniversry date to NEW EBA	EBA to End of Financial Year	Total Salary	SUPER	A/L Loading	Work Cover	Salary with Oncost
3	303			173	Health Workforce Consultant	44565		1/4/24	Secondment	76	L6	1	99357	Health Workforce	2	105290	105290	107395.8	50086.81644	882.7052055	52373.84219	103343.3638	11367.77002	417.3482001	2066.867277	117195.3493
4	316			21	Executive Coordinator	44684		5/3/24	Permanent	76	L4	1	77112	Health Workforce	2	83045	83045	84705.9	38872.89863	28544.72795	13460.11562	80877.74219	8896.551641	326.6216512	1617.554844	91718.47033
5	97			173	Health Workforce Consultant	42978		8/31/23	Permanent	60.8	L6	3	109305	Health Workforce	3	109305	66457.44	67786.5888	#VALUE!	36834.28767	33614.71938	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
6	350			178	Health Workforce Support Officer	44812		9/8/23	Permanent	76	L4	1	77112	Health Workforce	2	83045	83045	84705.9	14366.07123	26164.86301	42004.84356	82535.77781	9078.935559	333.3175642	1650.715556	93598.74649
7	207			280	Health Workforce Development Manager	43578		4/23/24	Permanent	76	L8	1	125793	Health Workforce	2	130360	130360	132967.2	102012.9534	41165.18795	24771.97151	167950.1129	18474.51242	678.2600712	3359.002258	190461.8876
8	332			44	Health Workforce Programs Manager	44781		8/8/23	Permanent	76	L8	3	134927	Health Workforce	3	134927	134927	137625.54	13677.53151	53970.8	68247.18559	135895.5171	14948.50688	548.808819	2717.910342	154110.7431
9	179			48	Health Workforce Consultant	43381		10/8/23	Permanent	76	L6	2	105290	Health Workforce	3	109305	109305	111491.1	28269.64384	25454.58904	55287.3674	109011.6003	11991.27603	440.239155	2180.232005	123623.3475
10	339			320	Health Workforce Support Officer	44802		8/29/23	Permanent	76	L4	1	77112	Health Workforce	2	83045	83045	84705.9	12253.4137	28440.06849	42004.84356	82698.32575	9096.815833	333.9740079	1653.966515	93783.08211
11	346			61	Health Workforce Support Officer	44809		9/5/23	Permanent	76	L4	1	77112	Health Workforce	2	83045	83045	84705.9	13732.27397	26847.42466	42004.84356	82584.54219	9084.299641	333.5144973	1651.690844	93654.04717
12	296			264	Health Workforce Policy and Strategy Lead	44518		11/18/23	Permanent	76	L7	2	117152	Health Workforce	3	121830	121830	124266.6	44614.04932	14686.35616	61622.61534	120923.0208	13301.53229	488.3429687	2418.460416	137131.3565
13	217			296	Health Workforce Coordinator - Locum	43675		7/29/23	Permanent	76	L5	1	87493	Health Workforce	2	93426	93426	95294.52	6472.084932	39930.01644	47255.63868	93657.74005	10302.35141	378.233181	1873.154801	106211.4794
14	314			247	Health Workforce Senior Coordinator - Outreach	44627		3/7/24	Permanent	76	L6	1	99357	Health Workforce	2	105290	105290	107395.8	67780.52877	19419.51452	33837.03288	121037.0762	13314.07838	488.8035768	2420.741523	137260.6996
15	330			60	Health Workforce Consultant	44762		7/20/23	Permanent	76	L6	1	99357	Health Workforce	2	105290	105290	107395.8	4899.79726	47596.84932	53256.5474	105753.194	11632.85134	427.0802064	2115.063879	119928.1894
16	41			248	Health Workforce Coordinator - Education	38978		9/18/23	Permanent	60.8	L5	3	96036	Health Workforce	3	96036	58389.888	59557.68576	20522.76164	27626.79452	29534.08527	77683.64143	8545.200557	392.1529976	1553.672829	88174.66782
17	277			162	Health Workforce Education Team Lead	44158		11/23/23	Permanent	76	L7	1	111221	Health Workforce	2	117152	117152	119495.04	43878.96986	12517.61096	59256.44449	115653.0253	12721.83278	467.0602945	2313.060506	131154.9789
18	331			319	Health Workforce Coordinator - Policy and Strategy	44781		8/8/23	Permanent	76	L5	1	87493	Health Workforce	2	93426	93426	95294.52	8869.153425	37370.4	47255.63868	93495.19211	10284.47113	377.5767374	1869.903842	106027.1438
19	220			59	Health Workforce Access Team Lead	43696		8/19/23	Permanent	76	L7	3	121830	Health Workforce	3	121830	121830	124266.6	16021.47945	45060.41096	61622.61534	122704.5058	13497.49563	495.5374271	2454.090115	139151.6289
20	224			47	Health Workforce Engagement Team Lead	43731		9/23/23	Permanent	76	L7	2	117152	Health Workforce	3	121830	121830	124266.6	26640.04384	33378.08219	61622.61534	121640.7414	13380.48155	491.2414555	2432.814827	137945.2792
21	364			323	Workforce Planning Consultant	44886		11/21/23	Permanent	76	L6	1	99357	Health Workforce	2	105290	105290	107395.8	38653.95616	11827.09589	53256.5474	103737.5995	11411.13594	418.9403055	2074.751989	117642.4277
22	230			50	Future Health Workforce Consultant	43746		10/8/23	Permanent	76	L6	2	105290	Health Workforce	3	109305	109305	111491.1	28269.64384	25454.58904	55287.3674	109011.6003	11991.27603	440.239155	2180.232005	123623.3475
23	66			52	Health Workforce Coordinator	36360		7/19/23	Permanent	55.6	L5	3	96036	Health Workforce	3	96036	53396.016	54463.93632	4472.909589	43676.64658	27008.14376	75157.69993	8267.346992	414.8854769	1503.153999	85343.0864
24
25
26
27
28
29
30
31
32
33																		Form1	Form2
34																		0.00497226	1810.04157	CASE 1Date in Column H < 01/01/2024
35																		0.08422082	179634.926	Formula 1: (Column H date - subtract 01/07/2023) / (amount column M / 365) * (ex 01/01/2024 - 01/07/2023)
36																		25.0546178	603.840781	Formula 2: (amount column M / 365) / (Column H date - subtract 01/07/2023) * (ex 01/01/2024 - 01/07/2023)
37																		37.5593293	352.109589
38
39																				Case 2Column H >= 01/01/2024
40																				Formula 1: substract 30 june 2024 - date in column H / amount column R * (01/01/2024- column H date)
41																				Formula: amount column R / substract 30 june 2024 - date in column H * (01/01/2024- column H date)
Sheet2

Cell Formulas
Range	Formula
R34:R37	R34	=LET( Case,IFS(H3<DATE(2024,1,1),"C1",H3>=DATE(2024,1,1),"C2"), subsdate,IFS(Case="C1",DATEDIF(DATE(2023,7,1),H3,"d"),Case="C2",DATEDIF(H3,DATE(2024,6,30),"D")), Amt,IFS(Case="C1",M3/365,Case="C2",R3), Muldiffdate,IFS(Case="C1",DATEDIF(H3,DATE(2024,1,1),"D"),Case="C2",DATEDIF(DATE(2024,1,1),H3,"d")), subsdate/Amt*Muldiffdate)
S34:S37	S34	=LET( Case,IFS(H3<DATE(2024,1,1),"C1",H3>=DATE(2024,1,1),"C2"), subsdate,IFS(Case="C1",DATEDIF(DATE(2023,7,1),H3,"d"),Case="C2",DATEDIF(H3,DATE(2024,6,30),"D")), Amt,IFS(Case="C1",M3/365,Case="C2",R3), Muldiffdate,IFS(Case="C1",DATEDIF(H3,DATE(2024,1,1),"D"),Case="C2",DATEDIF(DATE(2024,1,1),H3,"d")), Amt/subsdate*Muldiffdate)