Count the number of days from an end date for a given month

[EmmaTM]

Hi,
What would be the best formula to use in column L where I want to state the number of days for the given month = Q1 30/11/21
So all but cell L9 would be 30 as the end date is past 30/11/21 & L9 would be 18 as there are only 18 days of November left.

Thank you

 

[EmmaTM]

Yes I am making sure N2-P2 come back to D2 if C is N

 

[Toadstool]

I was trying to give a clue

=IF(C2="Y",P2-D2,IF(C2="N",SUM(N2:P2)-D2))

 

[EmmaTM]

Ah so close!

 

[EmmaTM]

Is this it?

EmmaTM.xlsx
	D	E	F				J		L
1	Amount	Start Date	End Date				Total Days		Days in Period
2	€ 250.00	14-Mar-21	13-Mar-22				365		30
3	€ 516.00	21-Sep-21	24-Dec-21				95		30
4	€ 4,062.00	01-Mar-21	28-Feb-22				365		30
5	€ 737.00	01-Mar-21	28-Feb-22				365		30
6	€ 600.00	01-Mar-21	28-Feb-22				365		30
7	€ 1,222.00	01-Mar-21	28-Feb-22				365		30
8	€ 74.00	01-Mar-21	28-Feb-22				365		30
9	€ 156.00	19-Aug-21	18-Nov-21				92		18
10	€ 715.00	01-Feb-21	21-Jan-22				355		30
11	€ 664.00	01-Nov-21	31-Jan-22				92		30
12	€ 128.00	01-Nov-21	31-Jan-22				92		30
13	€ 2,234.00	01-Apr-22	30-Apr-22				30		30
14	€ 2,233.00	01-Apr-22	30-Apr-22				30		30
15	€ 432.00	01-Jan-21	01-Jan-21				1		30
16	€ 234.00	22-Oct-21	01-Dec-21				41		30
17	€ 929.00	01-Jan-20	30-Oct-21				669		30
2nd

Cell Formulas
Range	Formula
J2:J17	J2	=F2-E2+1
L2:L17	L2	=IF(AND($F2<=$Q$1,$F2>EOMONTH($Q$1,-1)),DAY($F2),DAY(EOMONTH($Q$1,0)))

I have now rolled this file over to the next month where the current period is now 31/12/21.
You see row 9 before we wanted column L9 to show 18 days, this is now showing as 31 when it should be 0.
The sum is:
=IF(AND(F9<=Summary!$K$2,F9>EOMONTH(Summary!$K$2,-1)),DAY(F9),IF(E9>Summary!$K$2,0,DAY(EOMONTH(Summary!$K$2,0))))

Note summaryK2 is the date 31/12/21

Also column K days past should be 92 as thats the total number of days for that item =IF(Summary!$K$1-E9<0,0,Summary!$K$1-E9+1)

I also need to revise the formula in column N as rows 14 & 15 should have a BF balance of £2,234 & £2,233.45.
It is currently
=D11/J11*K11
I tried =IF(K13="0",D13,IF(K13>0,(D13/J13)*K13))
But this is giving me FALSE, can you tell me what I have wrong with the formula?

Thank you

 

[Toadstool]

It appears the end of month date which started in AA1 then moved to Q1 is now in a seperate sheet called Summary in cell $K$2. You don't say what is in Summary $K$1 but I'll assume it's the 1st of that month, like this:

EmmaTM.xlsx
	J	K	L
1		01-Dec-21
2		31-Dec-21
3
Summary

Cell Formulas
Range	Formula
K1	K1	=EOMONTH(K2,-1)+1

I've changed the Days in Period calculation as I now realize you want the count of how many days in the selected month are within the Start to End range.

I suspect you want the "Days Past" to be days between start date and the minimum of end date or end of month being accounted. I have changed the formula accordingly.

I suspect your Balance BF is to identify what amount of the contract has not yet been accounted for so it would be Amount divided by Total Days ($D2/$J2) multiplied by Total Days less Days Past ($J2-$K2). Let me know if that's wrong.

You don't say what your P&L or Balance CF calculations are but I'd like to see them as they need rounding to correctly perform the Ckeck column T.

Don't forget to test some items starting in 2022 with the "New Items"="Y" test.

EmmaTM.xlsx
	C	D	E	F				J	K	L	M	N	O	P	Q			T
1	New Item	Amount	Start Date	End Date				Total Days	Days Past	Days in Period	Days Prepaid	Balance BF	P&L	Balance CF				Check?
2	N	€ 250.00	14-Mar-21	13-Mar-22				365	263	31	?	€ 69.86	€ 21.23	€ 49.32				€ (109.59)
3	N	€ 516.00	29-Sep-21	24-Dec-21				87	64	24	?	€ 136.41	€ 226.00	€ 153.59				€ 0.00
4	N	€ 4,062.00	01-Mar-21	28-Feb-22				365	276	31	?	€ 990.46	€ 111.00	€ 2,960.54				€ 0.00
5	N	€ 737.00	01-Mar-21	28-Feb-22				365	276	31	?	€ 179.71	€ 147.40	€ 73.70				€ (336.19)
6	N	€ 600.00	01-Mar-21	28-Feb-22				365	276	31	?	€ 146.30	€ 120.00	€ 60.00				€ (273.70)
7	N	€ 1,222.00	01-Mar-21	28-Feb-22				365	276	31	?	€ 297.97	€ 244.40	€ 122.20				€ (557.43)
8	N	€ 74.00	01-Mar-21	28-Feb-22				365	276	31	?	€ 18.04	€ 14.80	€ 7.40				€ (33.76)
9	N	€ 156.00	19-Aug-21	18-Nov-21				92	92	0	?	€ -	€ 31.20	€ 15.60				€ (109.20)
10	N	€ 715.00	01-Feb-21	21-Jan-22				355	304	31	?	€ 102.72	€ 143.00	€ 71.50				€ (397.78)
11	N	€ 664.00	01-Nov-21	31-Jan-22				92	31	31	?	€ 440.26	€ 132.80	€ 66.40				€ (24.54)
12	N	€ 128.00	01-Nov-21	31-Jan-22				92	31	31	?	€ 84.87	€ 25.60	€ 12.80				€ (4.73)
13	N	€ 2,234.00	01-Apr-22	30-Apr-22				30	0	0	?	€ 2,234.00	€ (2,234.00)	€ 2,234.00				€ -
14	N	€ 2,233.00	01-Apr-22	30-Apr-22				30	0	0	?	€ 2,233.00	€ 446.60	€ 223.30				€ 669.90
15	N	€ 432.00	01-Jan-21	01-Jan-21				1	1	0	?	€ -	€ 86.40	€ 43.20				€ (302.40)
16	N	€ 234.00	22-Oct-21	01-Dec-21				41	41	1	?	€ -	€ 46.80	€ 23.40				€ (163.80)
17	N	€ 929.00	01-Jan-20	30-Oct-21				669	669	0	?	€ -	€ 185.80	€ 92.90				€ (650.30)
18	N	€ 323.00	30-Nov-21	05-Dec-21				6	2	5	?	€ 215.33	€ 64.60	€ 32.30				€ (10.77)
19	N	€ 221.00	01-Nov-21	01-Nov-21				1	1	0	?	€ -	€ 44.20	€ 22.10				€ (154.70)
4th

Cell Formulas
Range	Formula
J2:J19	J2	=F2-E2+1
K2:K19	K2	=IF(Summary!$K$1-$E2<0,0,MIN(Summary!$K$1,$F2)-$E2+1)
L2:L19	L2	=MAX(MIN(Summary!$K$2,$F2)-MAX(Summary!$K$1,$E2)+1,0)
N2:N19	N2	=($D2/$J2)*($J2-$K2)
T2:T19	T2	=IF($C2="Y",$P2-$D2,IF($C2="N",SUM($N2:$P2)-$D2))

It would be helpful if you could install XL2BB so we can better assist.

 

[EmmaTM]

That all worked - thank you so much

 

[Toadstool]

That all worked - thank you so much

You're welcome.
Thanks for letting me know.