# Calculate number of days in a particular month/year between two dates

1. ## Calculate number of days in a particular month/year between two dates

Hi All

I am trying to identify a formula that will allow me to identify how many rental days occured between two dates in a particular month for a report I have to produce each month.

The report shows cars out on rent which in some case have been out for over 12 months and I need to be able to calculate how many rental days have occured for each booking in the relevant reporting month. I am currently doing this manually by manipulating data but want to try and find an automated way so I can write a macro!

I have attached an example of the type of data I am presented with:  Register To Reply

2. ## Re: Calculate number of days in a particular month/year between two dates

In E2 put 30/04/11 The last date for April.

In d2 put =IF(MONTH(B2)=4,\$E\$2-B2,"") and format to Number 2 decimal places then copy down.  Register To Reply

3. ## Re: Calculate number of days in a particular month/year between two dates

Unfortunately that does not appear to provide the result i'm after. It should return a figure of 30 in D2 but is blank?  Register To Reply

4. ## Re: Calculate number of days in a particular month/year between two dates

Sorry I thought you were just interested In May End and Date Times.

Put 01/04/11 In E1
Put 30/04/11 In E2
Put =MONTH(E2 In E3)

Put this in D2 and copy it down

=IF(MONTH(B2)=\$E\$3,\$E\$2-B2,IF(MONTH(B2)>\$E\$3,\$E\$2-\$E\$1+1))  Register To Reply

5. ## Re: Calculate number of days in a particular month/year between two dates

Sorry this does not appear to be working either, it returns a result in D2 of 40635??  Register To Reply

6. ## Re: Calculate number of days in a particular month/year between two dates

Have you formatted the cells to Number with 2 Decimal places?  Register To Reply

7. ## Re: Calculate number of days in a particular month/year between two dates

Hi Timbo, apologies I put a date in the wrong place which caused the formula to fail.

I have correct my error however it appears that whilst it is working for some it does not work correctly for any rental that finished in April. An example is cell D6, it returns a result of 5.54 days however it should return a result of 24 days?

I have uploaded the spreadsheet again just in case i'm being a muppet. :-)  Register To Reply

8. ## Re: Calculate number of days in a particular month/year between two dates

No me being a muppet, one of those days!

Try this in D2

=IF(MONTH(B2)>\$E\$3,\$E\$2-\$E\$1+1,IF(MONTH(B2)=\$E\$3,B2-\$E\$1+1))  Register To Reply

9. ## Re: Calculate number of days in a particular month/year between two dates

Hi Timbo

No worries, I know the feeling! really appreciate you taking the time to help me.

Ok have input the new formula and it appears we are nearly there however (sorry) in cell D22 it is returning a result of 30 days but the rental only started in April so should only return a result of 15 days?  Register To Reply

10. ## Re: Calculate number of days in a particular month/year between two dates

Try this formula in D2 copied down

=MAX(0,MIN(B2,E\$2+1)-MAX(A2,E\$1))

That takes the times into account as well so it will give you fractional days, is that what you want?  Register To Reply

11. ## Re: Calculate number of days in a particular month/year between two dates

Excellent, many thanks both for all your help.

Not wishing to take the "P" but we provide our customers with a 59 minute grace period in any rental therefore in order for me to make this report 100% accurate I need to try and factor this in, would this be possible?

An example would be as follows:

Rental starts 01/04/2011 09.00 and finishes 02/04/2011 10.00. We would see that as 2 days.  Register To Reply

12. ## Re: Calculate number of days in a particular month/year between two dates

Perhaps subtract an hour and then round up - that would give you an integer, e.g. 1 day 12 hours would round to 2 days...so would 2 days 59 minutes.....but 2 days 1 hour 10 mins would round to 3 days.

To do that with my suggested formula change to

=CEILING(MAX(0,MIN(B2,E\$2+1)-MAX(A2,E\$1)-"1:00"),1)  Register To Reply

13. ## Re: Calculate number of days in a particular month/year between two dates

Used the formula on my report and whilst there is the odd one where i'm a day out (i.e D23 should be 9 days but shows 10) it gets me 99% of the way there and I think I can run with that. Unless of course you can think of a way to eliminate these errors?

Thank you very much for all your help, much appreciated.  Register To Reply

14. ## Re: Calculate number of days in a particular month/year between two dates

Hi Brothers Hope all is fine I was facing the same issue and will be much appreciated if I want to advise which equation will help of I want to count the number of days but for 3 Months not only April ?? for example April May and June

Thanks for your advise "note the number of hours is not important in my case as it will be for real estate company"  Register To Reply

15. ## Re: Calculate number of days in a particular month/year between two dates

I am new to the post and I seek some advise from all of you. I have similar situation to this thread. I need a formula for calculate number of days between time stamp but round up if 1 hour over. Please see attached excel.My formula did not seem to work  Register To Reply

16. ## Re: Calculate number of days in a particular month/year between two dates

Hello Mys228

Please can you post this as a new thread - that's always preferable for us......and you will find you get more interest in a new thread with no replies

Thanks  Register To Reply