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

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

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 Date End date Jan 2019 Feb 2019 Mar 2019 1/1/2018 2/27/2019 31 27 0

Thanks for your help!

2. 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 DATE END DATE DIFFERENCE FORMULA UNIT – DIFFERENCE CALCULATED IN.. 2 01/01/2010 02/18/2015 1874 =DATEDIF(A2,B2,”D”) Days (gives same result as =B2-A2) 3 61 =DATEDIF(A2,B2,”M”) Months 4 5 =DATEDIF(A2,B2,”Y”) Years 5 17 =DATEDIF(A2,B2,”MD”) Days, ignoring month and year (days from 1st to 18th) 6 1 =DATEDIF(A2,B2,”YM”) months, ignoring year (months from Jan to Feb) 7 48 =DATEDIF(A2,B2,”YD”) days, ignoring year (days from 1/1

Hope this helps.

3. 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...
 A B C D E F 1 Start Date End Date Jan 2019 Feb 2019 Mar 2019 2 1/1/2018 2/27/2019 31 27 0 3 11/4/2017 1/17/2019 17 0 0

Thanks again for your help!

4. 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).

5. 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!

6. …not been back to your computer recently then?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•