Need a formula that calculates how many days in a given month between two given dates

elik

New member
Joined
Feb 18, 2020
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2019
Can someone please provide a formula for cells C2, D2, E2 etc... that will give me the number of days within the start and end dates in columns A and B for the month entered in the column headers? Please include a formula that takes in to account the years as well Meaning I don't want the formula to calculate 62 day for the month of Jan since I only want Jan of 2019). This formula will be embedded in to a very large spreadsheet. So if you have an option for a formula that doesn't take too long to recalc would be great!

Start DateEnd dateJan 2019Feb 2019Mar 2019
1/1/20182/27/201931270

Thanks for your help!
 
Apply any one of these based on your needs in C,D,E etc. of course be sure to change the column in the short program.

START DATEEND DATEDIFFERENCEFORMULAUNIT – DIFFERENCE CALCULATED IN..
201/01/201002/18/20151874=DATEDIF(A2,B2,”D”)Days (gives same result as =B2-A2)
361=DATEDIF(A2,B2,”M”)Months
45=DATEDIF(A2,B2,”Y”)Years
517=DATEDIF(A2,B2,”MD”)Days, ignoring month and year (days from 1st to 18th)
61=DATEDIF(A2,B2,”YM”)months, ignoring year (months from Jan to Feb)
748=DATEDIF(A2,B2,”YD”)days, ignoring year (days from 1/1

Hope this helps.
 
Thanks for your response -
However, can you please provide me with an actual formula that I can use in cell D2 that would give me 31 and E2 that will give me 27 etc...
ABCDEF
1Start DateEnd DateJan 2019Feb 2019Mar 2019
21/1/20182/27/201931270
311/4/20171/17/20191700

Thanks again for your help!
 
Can't tell what's in D1,E1,F1 etc. Is it a real excel date formatted to show just month and year or is it just a literal string (if you're lucky, it might not matter!).
It might be in D2:
Code:
=MAX(0,MIN($C2,EOMONTH(D$1,0))-MAX(D$1-1,$B2))
copied across and down, but it depends on things such as how many days you want to show for a start date of 3 March 2019 and an end date of 4 March 2019. 1 or 2 days? If it's 1, then is the start date or the end date which is counted?
Supply a workbook with your setup - it will answer of a lot of questions without us having to guess (wrongly).
 
Last edited:
Thank you. I will supply a spreadsheet when I get back to my computer. To respond to your questions, the months in row 1 can be any format you tell that would work for this calculation.
If the date range is 1/1/2019-1/2/2019 I would like it account for two days.
Thanks for your help!
 
…not been back to your computer recently then?
 
Back
Top