Results 1 to 6 of 6

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

  1. #1
    Neophyte elik's Avatar
    Join Date
    Feb 2020
    Posts
    3
    Articles
    0
    Excel Version
    2019

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Acolyte bobjglover@gmail.com's Avatar
    Join Date
    Aug 2015
    Posts
    57
    Articles
    0
    Excel Version
    1901
    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. #3
    Neophyte elik's Avatar
    Join Date
    Feb 2020
    Posts
    3
    Articles
    0
    Excel Version
    2019
    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. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2020-02-18 at 11:55 PM.

  5. #5
    Neophyte elik's Avatar
    Join Date
    Feb 2020
    Posts
    3
    Articles
    0
    Excel Version
    2019
    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. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    …not been back to your computer recently then?

Tags for this Thread

Posting Permissions

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