Show remaining and received monthly figures, based on dates?

[jjrrcc11]

Part 1

I have a total yearly salary figure in cell C4, and I wish to have a remaining salary figure in cell C10, always based on today's date.
Salary is paid on the last day of the month, with the first payment having made on April 30th and the last one being due on March 31st 2024. Therefore, at present, this remaining salary figure should show total minus 1 month as there has been 1 out of the 12 payments. What formula can I use for this?


Part 2

Linking to Part 1 above, I also wish to have a Received to Date Salary figure in cell E3 which I would like to automatically update based on the current date. e.g. say the monthly salary is 2000, as there has only been 1 payment so far, it should show 2000, but in another month's time, it will show 4000, and so on.


Thanks in advance

 

[jjrrcc11]

Is anyone able to assist with this? Thanks

 

[Sufiyan97]

Try

Book3
	A	B	C	D	E	F	G	H	I
1
2					Received to Date Salary
3					2000
4
5
6
7
8
9			Remaining Salary as on Today			First Payment	Last Payment
10			22000			4/30/2023	3/31/2024
11			22000
12
13
14
15
16
17
18
19
20
21
22
23
24			24000
25
26
27
Sheet1

Cell Formulas
Range	Formula
E3	E3	=C24-C10
C10	C10	=C24/DATEDIF(F10,EOMONTH(G10,1),"m")*(DATEDIF(F10,EOMONTH(G10,1),"m")-(MONTH(TODAY())-MONTH(F10)))
C11	C11	=C24/DATEDIF(F10,EOMONTH(G10,1),"m")*(DATEDIF(F10,EOMONTH(G10,1),"m")-(DATEDIF(EOMONTH(F10,-1),TODAY(),"m")))

 

[jjrrcc11]

Try

Book3
	A	B	C	D	E	F	G	H	I
1
2					Received to Date Salary
3					2000
4
5
6
7
8
9			Remaining Salary as on Today			First Payment	Last Payment
10			22000			4/30/2023	3/31/2024
11			22000
12
13
14
15
16
17
18
19
20
21
22
23
24			24000
25
26
27
Sheet1

Cell Formulas
Range	Formula
E3	E3	=C24-C10
C10	C10	=C24/DATEDIF(F10,EOMONTH(G10,1),"m")*(DATEDIF(F10,EOMONTH(G10,1),"m")-(MONTH(TODAY())-MONTH(F10)))
C11	C11	=C24/DATEDIF(F10,EOMONTH(G10,1),"m")*(DATEDIF(F10,EOMONTH(G10,1),"m")-(DATEDIF(EOMONTH(F10,-1),TODAY(),"m")))

Amazing, thanks so much!
Are you able to explain how the C10 and C11 formula's work so I know for future, and also the difference between them? They produce the same result, so is it just a different way of achieving it?
Thanks again.

 

[Sufiyan97]

You're welcome.
they are just different way of achieving it.

The difference between them is just last part

=C24/DATEDIF(F10,EOMONTH(G10,1),"m")*(DATEDIF(F10,EOMONTH(G10,1),"m")-(MONTH(TODAY())-MONTH(F10)))

=C24/DATEDIF(F10,EOMONTH(G10,1),"m")*(DATEDIF(F10,EOMONTH(G10,1),"m")-(DATEDIF(EOMONTH(F10,-1),TODAY(),"m")))

We have used below formulas together to achieve the result

DATEDIF
EOMONTH
MONTH
TODAY

You can do a google search for each formula, so you will have better idea for each one.