IF STATEMENT WITH SUBTRACTING AND ADDING DATE

[ChetanPuri]

Hi Excel Gurus, trying to Build a forumla, where in example below: if date in column H3 is equal to less than 01 Jan 2024 then subtract H3- 01 July 2023 and divide the value in column Q/365 and multiply by number of days calculeted bewteen 2 days, otherwise 30 June 2024- H3 and then divide values in Column r and multiply by no. of days between 30 June & H3. Any help would be appreciated.

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	AA
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	R3/365	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

Cell Formulas
Range	Formula
S3	S3	=IF(AND(H3>=Summary!$B$3, H3<=Summary!$B$3),"Q3/365","R3/365")

 

[Joe4]

Anything placed between double-quotes is treated as literal text.
If you want to do the division, remove the double-quotes, i.e.

=IF(AND(H3>=Summary!$B$3, H3<=Summary!$B$3),Q3/365,R3/365)

but I don't see the subtraction part of your formula.
It seems that the parts like:

Q3/365

need to be something like:

(Q3-some value)/365

What exactly is in Summary!$B$3?

 

[Peter_SSs]

AND(H3>=Summary!$B$3, H3<=Summary!$B$3)

I don't understand why you have that in your formula. The only way that H3 can be greater than or equal to Summary!$B$3 and also less than or equal to Summary!$B$3 is if it is exactly equal to Summary!$B$3

Other than that, remove the quote marks to allow the actual calculation to proceed, as Joe has suggested. So guessing that you might try

=IF(H3=Summary!$B$3,Q3,R3)/365

 

[Joe4]

I don't understand why you have that in your formula. The only way that H3 can be greater than or equal to Summary!$B$3 and also less than or equal to Summary!$B$3 is if it is exactly equal to Summary!$B$3

Other than that, remove the quote marks to allow the actual calculation to proceed, as joe has suggested. So guessing that you might try

=IF(H3=Summary!$B$3,Q3,R3)/365)

Good point, Peter. I did not analyze that part of the formula, as I was focusing on the result.
I am guessing that they are probably want to check two different dates, not the same one.
They also mentioned something about subtracting value before dividing, but were not clear where those values to be subtracted are coming from.

 

[ChetanPuri]

Thank you Joe, much appreciated. Summary b3 is another worksheet, which is basically 01 January 2024. I was trying to build the salary budget, where up until anniversary date employees will get the current salary.past the anniversary date they will move on to next level column q and from 01 Jan 2024 they will be entitled to yearly increase based on inflation.
I was trying to build in column such as if anniversary date is less than 01 January 2024, it will calculate the number of days between anniversary date and 01 July 2023 and basically then calculate the salary for number days at current level.
If anniversary date is greater than or equal to 01 January 2024 when the new eba comes into effect then it should calculate current salaries from 31 Dec to 01 July 2023 and then from 01 January to anniversary date at a new rate without level increase.

 

[Joe4]

Thank you Joe, much appreciated. Summary b3 is another worksheet, which is basically 01 January 2024. I was trying to build the salary budget, where up until anniversary date employees will get the current salary.past the anniversary date they will move on to next level column q and from 01 Jan 2024 they will be entitled to yearly increase based on inflation.
I was trying to build in column such as if anniversary date is less than 01 January 2024, it will calculate the number of days between anniversary date and 01 July 2023 and basically then calculate the salary for number days at current level.
If anniversary date is greater than or equal to 01 January 2024 when the new eba comes into effect then it should calculate current salaries from 31 Dec to 01 July 2023 and then from 01 January to anniversary date at a new rate without level increase.

OK, based on the responses from myself and Peter, I think you should have all the information you need to amend you formula. Give it a try and see how you do.

If it does not work out, post back to this thread with the new formula you tried.