+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    05-18-2011
    Location
    Birmingham,England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Question 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:

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

    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.

  3. #3
    Registered User
    Join Date
    05-18-2011
    Location
    Birmingham,England
    MS-Off Ver
    Excel 2003
    Posts
    7

    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?

  4. #4
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

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

  5. #5
    Registered User
    Join Date
    05-18-2011
    Location
    Birmingham,England
    MS-Off Ver
    Excel 2003
    Posts
    7

    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??

  6. #6
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

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

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

  7. #7
    Registered User
    Join Date
    05-18-2011
    Location
    Birmingham,England
    MS-Off Ver
    Excel 2003
    Posts
    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. :-)
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

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

  9. #9
    Registered User
    Join Date
    05-18-2011
    Location
    Birmingham,England
    MS-Off Ver
    Excel 2003
    Posts
    7

    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?

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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?
    Last edited by daddylonglegs; 05-18-2011 at 08:12 AM.
    Audere est facere

  11. #11
    Registered User
    Join Date
    05-18-2011
    Location
    Birmingham,England
    MS-Off Ver
    Excel 2003
    Posts
    7

    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.

  12. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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)

  13. #13
    Registered User
    Join Date
    05-18-2011
    Location
    Birmingham,England
    MS-Off Ver
    Excel 2003
    Posts
    7

    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.

  14. #14
    Registered User
    Join Date
    12-25-2012
    Location
    Doha Qatar
    MS-Off Ver
    Excel 2007
    Posts
    1

    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"

  15. #15
    Registered User
    Join Date
    05-31-2014
    Location
    Redmond, WA
    MS-Off Ver
    2007-2010
    Posts
    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

  16. #16
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1