Looking for help with excel formula to determine number of days in a month

[sallysebern]

Hello!

I'm looking for help to come up with an excel formula to help me figure out how many days are in a month based on a date entered.

In B1 I have the delivery date.
In B2 I would like to have a formula that will tell me how many days of pro-rated rent.

B1: 10/9/2017
B2: 23

I tried using EOMONTH but this is giving me a total value of the month of October when I want to know how many days are between the 9th and the 31st. I also want to make sure that that end result counts the day and will account for the fluctuation in the days of the month.

Any help is appreciated.

Thanks!

[Glenn Kennedy]

Try this, formatted as general:

=EOMONTH(B1,0)-B1+1

[sallysebern]

YAY! I'm not sure why I couldn't wrap my head around this. Thank you for your help!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[sallysebern]

I have another issue, If I use this formula and the date is 11/1/17 its charging my customer 30 days of pro-rated rent. Would there be a way to write the formula to have the exception if the 1st is used to enter 0?

[Glenn Kennedy]

Try this:

=IF(DAY(B1)=1,0,EOMONTH(B1,0)-B1+1)

[sallysebern]

Thank you! That seems to be working great!

[Glenn Kennedy]

You're welcome.