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

E

EmmaTM

Board Regular
Joined
Jan 5, 2022
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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.
1641463693950.png


Thank you
 

Attachments

  • 1641463608771.png
    1641463608771.png
    15.9 KB · Views: 2

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
EmmaTM said:
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.
Click to expand...
Can we have clarification on this?
At first you said L9 would be 30
and then again L9 would be 18
 
Upvote 0
Hi EmmaTM,

Your description suggests the answer should be 12, as that's how many days there are left for November 2021 from 18 November 2021, so that's what I've calculated.

EmmaTM.xlsx
DEFJL
1AmountStart DateEnd DateTotal DaysDays in Period
2€ 250.0014-Mar-2113-Mar-2236530
3€ 516.0021-Sep-2124-Dec-219530
4€ 4,062.0001-Mar-2128-Feb-2236530
5€ 737.0001-Mar-2128-Feb-2236530
6€ 600.0001-Mar-2128-Feb-2236530
7€ 1,222.0001-Mar-2128-Feb-2236530
8€ 74.0001-Mar-2128-Feb-2236530
9€ 156.0019-Aug-2118-Nov-219212
10€ 715.0001-Feb-2121-Jan-2235530
11€ 664.0001-Nov-2131-Jan-229230
12€ 128.0001-Nov-2131-Jan-229230
13€ 2,234.0001-Apr-2230-Apr-223030
14€ 2,233.0001-Apr-2230-Apr-223030
15€ 432.0001-Jan-2101-Jan-21130
16€ 234.0022-Oct-2101-Dec-214130
17€ 929.0001-Jan-2030-Oct-2166930
Sheet1
Cell Formulas
RangeFormula
J2:J17J2=F2-E2+1
L2:L17L2=IF($F2<$Q$1,MIN(EOMONTH($Q$1,0)-$F2,DAY(EOMONTH($Q$1,0))),DAY(EOMONTH($Q$1,0)))
 
Upvote 0
Sufiyan97 said:
Can we have clarification on this?
At first you said L9 would be 30
and then again L9 would be 18
Click to expand...
Yes sure, "So all but cell L9 would be 30"

L9 should only be 18 as there are 18 days in Nov to the end date of 18/11/21. Everything else is 30 days as all the end dates are past 30/11/21

Thank you
 
Upvote 0
Toadstool said:
Hi EmmaTM,

Your description suggests the answer should be 12, as that's how many days there are left for November 2021 from 18 November 2021, so that's what I've calculated.

EmmaTM.xlsx
DEFJL
1AmountStart DateEnd DateTotal DaysDays in Period
2€ 250.0014-Mar-2113-Mar-2236530
3€ 516.0021-Sep-2124-Dec-219530
4€ 4,062.0001-Mar-2128-Feb-2236530
5€ 737.0001-Mar-2128-Feb-2236530
6€ 600.0001-Mar-2128-Feb-2236530
7€ 1,222.0001-Mar-2128-Feb-2236530
8€ 74.0001-Mar-2128-Feb-2236530
9€ 156.0019-Aug-2118-Nov-219212
10€ 715.0001-Feb-2121-Jan-2235530
11€ 664.0001-Nov-2131-Jan-229230
12€ 128.0001-Nov-2131-Jan-229230
13€ 2,234.0001-Apr-2230-Apr-223030
14€ 2,233.0001-Apr-2230-Apr-223030
15€ 432.0001-Jan-2101-Jan-21130
16€ 234.0022-Oct-2101-Dec-214130
17€ 929.0001-Jan-2030-Oct-2166930
Sheet1
Cell Formulas
RangeFormula
J2:J17J2=F2-E2+1
L2:L17L2=IF($F2<$Q$1,MIN(EOMONTH($Q$1,0)-$F2,DAY(EOMONTH($Q$1,0))),DAY(EOMONTH($Q$1,0)))
Click to expand...
I am actually looking to result the days passed in Nov which is 18 days. End date is 18/11, not how many days are left between 18/11 & 30/11.

Thank you
 
Upvote 0
Is this it?

EmmaTM.xlsx
DEFJL
1AmountStart DateEnd DateTotal DaysDays in Period
2€ 250.0014-Mar-2113-Mar-2236530
3€ 516.0021-Sep-2124-Dec-219530
4€ 4,062.0001-Mar-2128-Feb-2236530
5€ 737.0001-Mar-2128-Feb-2236530
6€ 600.0001-Mar-2128-Feb-2236530
7€ 1,222.0001-Mar-2128-Feb-2236530
8€ 74.0001-Mar-2128-Feb-2236530
9€ 156.0019-Aug-2118-Nov-219218
10€ 715.0001-Feb-2121-Jan-2235530
11€ 664.0001-Nov-2131-Jan-229230
12€ 128.0001-Nov-2131-Jan-229230
13€ 2,234.0001-Apr-2230-Apr-223030
14€ 2,233.0001-Apr-2230-Apr-223030
15€ 432.0001-Jan-2101-Jan-21130
16€ 234.0022-Oct-2101-Dec-214130
17€ 929.0001-Jan-2030-Oct-2166930
2nd
Cell Formulas
RangeFormula
J2:J17J2=F2-E2+1
L2:L17L2=IF(AND($F2<=$Q$1,$F2>EOMONTH($Q$1,-1)),DAY($F2),DAY(EOMONTH($Q$1,0)))
 
Upvote 0
Toadstool said:
Is this it?

EmmaTM.xlsx
DEFJL
1AmountStart DateEnd DateTotal DaysDays in Period
2€ 250.0014-Mar-2113-Mar-2236530
3€ 516.0021-Sep-2124-Dec-219530
4€ 4,062.0001-Mar-2128-Feb-2236530
5€ 737.0001-Mar-2128-Feb-2236530
6€ 600.0001-Mar-2128-Feb-2236530
7€ 1,222.0001-Mar-2128-Feb-2236530
8€ 74.0001-Mar-2128-Feb-2236530
9€ 156.0019-Aug-2118-Nov-219218
10€ 715.0001-Feb-2121-Jan-2235530
11€ 664.0001-Nov-2131-Jan-229230
12€ 128.0001-Nov-2131-Jan-229230
13€ 2,234.0001-Apr-2230-Apr-223030
14€ 2,233.0001-Apr-2230-Apr-223030
15€ 432.0001-Jan-2101-Jan-21130
16€ 234.0022-Oct-2101-Dec-214130
17€ 929.0001-Jan-2030-Oct-2166930
2nd
Cell Formulas
RangeFormula
J2:J17J2=F2-E2+1
L2:L17L2=IF(AND($F2<=$Q$1,$F2>EOMONTH($Q$1,-1)),DAY($F2),DAY(EOMONTH($Q$1,0)))
Click to expand...
Thank you that did work however, it has thrown up another issue:
See the 2 highlighted items rows 13 & 14, these should be zero as the start date is after the current period of 31/11/21.
I am not currently looking at the start date in the formula.

1641473086230.png
 
Upvote 0
EmmaTM.xlsx
DEFJL
1AmountStart DateEnd DateTotal DaysDays in Period
2€ 250.0014-Mar-2113-Mar-2236530
3€ 516.0021-Sep-2124-Dec-219530
4€ 4,062.0001-Mar-2128-Feb-2236530
5€ 737.0001-Mar-2128-Feb-2236530
6€ 600.0001-Mar-2128-Feb-2236530
7€ 1,222.0001-Mar-2128-Feb-2236530
8€ 74.0001-Mar-2128-Feb-2236530
9€ 156.0019-Aug-2118-Nov-219218
10€ 715.0001-Feb-2121-Jan-2235530
11€ 664.0001-Nov-2131-Jan-229230
12€ 128.0001-Nov-2131-Jan-229230
13€ 2,234.0001-Apr-2230-Apr-22300
14€ 2,233.0001-Apr-2230-Apr-22300
15€ 432.0001-Jan-2101-Jan-21130
16€ 234.0022-Oct-2101-Dec-214130
17€ 929.0001-Jan-2030-Oct-2166930
18€ 323.0011/30/202112/5/2021630
19€ 221.0011/1/202111/1/202111
3rd
Cell Formulas
RangeFormula
J2:J19J2=F2-E2+1
L2:L19L2=IF(AND($F2<=$Q$1,$F2>EOMONTH($Q$1,-1)),DAY($F2),IF($E2>$Q$1,0,DAY(EOMONTH($Q$1,0))))
 
Upvote 0
Thank you Toadstool that worked perfectly.
Could you tell me what is wrong with this check calc in column T that I am doing or should I start a new thread - I am hoping it is a simple fix!
It seems to be the second IF statement if column C is N that isnt working.

=IF(C2="Y",P2-D2,IF(C2="N",(N2:P2)-D2))
1641547922199.png
 

Attachments

  • 1641547844095.png
    1641547844095.png
    21 KB · Views: 2
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Formula to Subtract a Given Date from the Beginning of the Month of Same Date
Replies
7
Views
163
Joe4
Joe4
K
  • Solved
Prorating Salary based on hire date and Term date - by month
Replies
4
Views
170
KH Consulting
K
M
  • Question
Excel - Identify number of business days for a specific month within a weekly date range
Replies
3
Views
512
duggie33
D
H
  • Question
Display month based on calculations
Replies
1
Views
282
awoohaw
awoohaw
L
  • Question
Using DATEDIF with multiple date criteria
Replies
3
Views
336
Eric W
Eric W

Forum statistics

Threads
1,215,472
Messages
6,125,004
Members
449,203
Latest member
Daymo66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top