Calculating the number of days between two dates in Excel The Next CEO of Stack OverflowGetting Networkdays to accurately reflect the number of days between two datesExcel: Calculating working hours between two datesHow to convert decimal years into a date (format dd-mmm-yyyy or similar) in either Excel or SPSS?How autofill all rows between two dates in excel with the second date as the incrementing limit?Determine days in Excel formulaCount number of days between two dates including start and end dateHow do I calculate the difference between two dates?Excel formula for calculating daysCalculate the number of days per year between two different dates ExcelCalculate date when age plus service equals specific value in Excel

Is it okay to majorly distort historical facts while writing a fiction story?

How can I separate the number from the unit in argument?

Is this a new Fibonacci Identity?

How to implement Comparable so it is consistent with identity-equality

Is the 21st century's idea of "freedom of speech" based on precedent?

"Eavesdropping" vs "Listen in on"

Why can't we say "I have been having a dog"?

Why does the freezing point matter when picking cooler ice packs?

How to find if SQL server backup is encrypted with TDE without restoring the backup

How badly should I try to prevent a user from XSSing themselves?

Car headlights in a world without electricity

Could you use a laser beam as a modulated carrier wave for radio signal?

Early programmable calculators with RS-232

Find a path from s to t using as few red nodes as possible

What is the difference between 'contrib' and 'non-free' packages repositories?

Is it possible to create a QR code using text?

Creating a script with console commands

Is it a bad idea to plug the other end of ESD strap to wall ground?

What happens if you break a law in another country outside of that country?

Why does sin(x) - sin(y) equal this?

Can a PhD from a non-TU9 German university become a professor in a TU9 university?

How dangerous is XSS

That's an odd coin - I wonder why

How did scripture get the name bible?



Calculating the number of days between two dates in Excel



The Next CEO of Stack OverflowGetting Networkdays to accurately reflect the number of days between two datesExcel: Calculating working hours between two datesHow to convert decimal years into a date (format dd-mmm-yyyy or similar) in either Excel or SPSS?How autofill all rows between two dates in excel with the second date as the incrementing limit?Determine days in Excel formulaCount number of days between two dates including start and end dateHow do I calculate the difference between two dates?Excel formula for calculating daysCalculate the number of days per year between two different dates ExcelCalculate date when age plus service equals specific value in Excel










9















I consider 7 days to be a week,
so if I look at 1 January 2019 - 7 January 2019,
I would say that is 7 days. 
But often I seen when people are calculating the number of days between two dates or age they use the formula B1-A1 where A1 is the Start Date/DoB
and B1 is the end date or today's date. 
This would give 6 for the above example.



I use (B1-A1)+1 which gives 7
and for dates of birth I would use ((B1-A1)+1)/365.25
Which is correct?










share|improve this question



















  • 2





    7 - 1 is 6, no? What is correct depends on your definition and whether you include timestamps or not. When subtracting 20190101T00:00:00 from 20190107T23:59:59, you get 6.999, which can be rounded appropriately.

    – slhck
    Mar 25 at 12:38











  • @slhck That really makes sense when you think about the time as well as date.

    – Naz
    Mar 25 at 12:54






  • 7





    If start date/dob and today are the same day, do you want the answer to be 0 or 1? B1-A1 is the number of complete days that have past.

    – Forward Ed
    Mar 25 at 13:28






  • 2





    You're asking whether exclusive or inclusive operations are correct. They're both correct, depending on what you are trying to achieve. See @Chris Rogers's answer below.

    – studog
    Mar 25 at 18:56











  • (1) Well, there’s also the point of view that there are 5 days between 1 January and 7 January.  See this.  (2) If a person was born on 27 March 1959, on what day did they turn 20?  27 March 1979.  Now, if B1 = 27 March 1979 and A1 = 27 March 1959, what does your formula say?  What does it say for B1 = 26 March 1979?

    – Scott
    Mar 27 at 5:14















9















I consider 7 days to be a week,
so if I look at 1 January 2019 - 7 January 2019,
I would say that is 7 days. 
But often I seen when people are calculating the number of days between two dates or age they use the formula B1-A1 where A1 is the Start Date/DoB
and B1 is the end date or today's date. 
This would give 6 for the above example.



I use (B1-A1)+1 which gives 7
and for dates of birth I would use ((B1-A1)+1)/365.25
Which is correct?










share|improve this question



















  • 2





    7 - 1 is 6, no? What is correct depends on your definition and whether you include timestamps or not. When subtracting 20190101T00:00:00 from 20190107T23:59:59, you get 6.999, which can be rounded appropriately.

    – slhck
    Mar 25 at 12:38











  • @slhck That really makes sense when you think about the time as well as date.

    – Naz
    Mar 25 at 12:54






  • 7





    If start date/dob and today are the same day, do you want the answer to be 0 or 1? B1-A1 is the number of complete days that have past.

    – Forward Ed
    Mar 25 at 13:28






  • 2





    You're asking whether exclusive or inclusive operations are correct. They're both correct, depending on what you are trying to achieve. See @Chris Rogers's answer below.

    – studog
    Mar 25 at 18:56











  • (1) Well, there’s also the point of view that there are 5 days between 1 January and 7 January.  See this.  (2) If a person was born on 27 March 1959, on what day did they turn 20?  27 March 1979.  Now, if B1 = 27 March 1979 and A1 = 27 March 1959, what does your formula say?  What does it say for B1 = 26 March 1979?

    – Scott
    Mar 27 at 5:14













9












9








9


2






I consider 7 days to be a week,
so if I look at 1 January 2019 - 7 January 2019,
I would say that is 7 days. 
But often I seen when people are calculating the number of days between two dates or age they use the formula B1-A1 where A1 is the Start Date/DoB
and B1 is the end date or today's date. 
This would give 6 for the above example.



I use (B1-A1)+1 which gives 7
and for dates of birth I would use ((B1-A1)+1)/365.25
Which is correct?










share|improve this question
















I consider 7 days to be a week,
so if I look at 1 January 2019 - 7 January 2019,
I would say that is 7 days. 
But often I seen when people are calculating the number of days between two dates or age they use the formula B1-A1 where A1 is the Start Date/DoB
and B1 is the end date or today's date. 
This would give 6 for the above example.



I use (B1-A1)+1 which gives 7
and for dates of birth I would use ((B1-A1)+1)/365.25
Which is correct?







microsoft-excel date-time date






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 27 at 5:10









Scott

16.1k113990




16.1k113990










asked Mar 25 at 12:12









NazNaz

17912




17912







  • 2





    7 - 1 is 6, no? What is correct depends on your definition and whether you include timestamps or not. When subtracting 20190101T00:00:00 from 20190107T23:59:59, you get 6.999, which can be rounded appropriately.

    – slhck
    Mar 25 at 12:38











  • @slhck That really makes sense when you think about the time as well as date.

    – Naz
    Mar 25 at 12:54






  • 7





    If start date/dob and today are the same day, do you want the answer to be 0 or 1? B1-A1 is the number of complete days that have past.

    – Forward Ed
    Mar 25 at 13:28






  • 2





    You're asking whether exclusive or inclusive operations are correct. They're both correct, depending on what you are trying to achieve. See @Chris Rogers's answer below.

    – studog
    Mar 25 at 18:56











  • (1) Well, there’s also the point of view that there are 5 days between 1 January and 7 January.  See this.  (2) If a person was born on 27 March 1959, on what day did they turn 20?  27 March 1979.  Now, if B1 = 27 March 1979 and A1 = 27 March 1959, what does your formula say?  What does it say for B1 = 26 March 1979?

    – Scott
    Mar 27 at 5:14












  • 2





    7 - 1 is 6, no? What is correct depends on your definition and whether you include timestamps or not. When subtracting 20190101T00:00:00 from 20190107T23:59:59, you get 6.999, which can be rounded appropriately.

    – slhck
    Mar 25 at 12:38











  • @slhck That really makes sense when you think about the time as well as date.

    – Naz
    Mar 25 at 12:54






  • 7





    If start date/dob and today are the same day, do you want the answer to be 0 or 1? B1-A1 is the number of complete days that have past.

    – Forward Ed
    Mar 25 at 13:28






  • 2





    You're asking whether exclusive or inclusive operations are correct. They're both correct, depending on what you are trying to achieve. See @Chris Rogers's answer below.

    – studog
    Mar 25 at 18:56











  • (1) Well, there’s also the point of view that there are 5 days between 1 January and 7 January.  See this.  (2) If a person was born on 27 March 1959, on what day did they turn 20?  27 March 1979.  Now, if B1 = 27 March 1979 and A1 = 27 March 1959, what does your formula say?  What does it say for B1 = 26 March 1979?

    – Scott
    Mar 27 at 5:14







2




2





7 - 1 is 6, no? What is correct depends on your definition and whether you include timestamps or not. When subtracting 20190101T00:00:00 from 20190107T23:59:59, you get 6.999, which can be rounded appropriately.

– slhck
Mar 25 at 12:38





7 - 1 is 6, no? What is correct depends on your definition and whether you include timestamps or not. When subtracting 20190101T00:00:00 from 20190107T23:59:59, you get 6.999, which can be rounded appropriately.

– slhck
Mar 25 at 12:38













@slhck That really makes sense when you think about the time as well as date.

– Naz
Mar 25 at 12:54





@slhck That really makes sense when you think about the time as well as date.

– Naz
Mar 25 at 12:54




7




7





If start date/dob and today are the same day, do you want the answer to be 0 or 1? B1-A1 is the number of complete days that have past.

– Forward Ed
Mar 25 at 13:28





If start date/dob and today are the same day, do you want the answer to be 0 or 1? B1-A1 is the number of complete days that have past.

– Forward Ed
Mar 25 at 13:28




2




2





You're asking whether exclusive or inclusive operations are correct. They're both correct, depending on what you are trying to achieve. See @Chris Rogers's answer below.

– studog
Mar 25 at 18:56





You're asking whether exclusive or inclusive operations are correct. They're both correct, depending on what you are trying to achieve. See @Chris Rogers's answer below.

– studog
Mar 25 at 18:56













(1) Well, there’s also the point of view that there are 5 days between 1 January and 7 January.  See this.  (2) If a person was born on 27 March 1959, on what day did they turn 20?  27 March 1979.  Now, if B1 = 27 March 1979 and A1 = 27 March 1959, what does your formula say?  What does it say for B1 = 26 March 1979?

– Scott
Mar 27 at 5:14





(1) Well, there’s also the point of view that there are 5 days between 1 January and 7 January.  See this.  (2) If a person was born on 27 March 1959, on what day did they turn 20?  27 March 1979.  Now, if B1 = 27 March 1979 and A1 = 27 March 1959, what does your formula say?  What does it say for B1 = 26 March 1979?

– Scott
Mar 27 at 5:14










3 Answers
3






active

oldest

votes


















16














It is not a question of one formula being right and the other being wrong. It is a question of what you are looking for.



Say you are working on a task 24 hours a day. You begin the task 17 January 2000 at 9:00 AM and complete the task 18 January 2000 at 9:00 AM. If the question is How many days did the task take ? You would take the difference; get 24 hours and answer 1 day.

If, however, the question is On how many days did you work on the task ? You would immediately respond 2



Thus =B1 - A1 or =B1 - A1 + 1 might be appropriate depending on what you are trying to measure.






share|improve this answer
































    15














    The answer revolves around how Excel deals with dates. When days between dates are calculated using formulae such as B1-A1, Excel would turn the dates into serial numbers and use the serial numbers to calculate the number or days in between.




    By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. (Source: Microsoft)




    So the days between January 1, 1900 and January 7, 1900 would equal 7 minus 1 equalling 6.



    The same would apply with 1/1/2008 - 7/1/2008



    January 1, 2008 is 39,447 and
    January 7, 2008 is 39,453



    39,453 — 39,447 = 6



    If you wish to count the days inclusive you would need to add 1 to make the formula to be for example B1-A1+1.



    If you want to calculate days exclusive you would need to minus 1 day making B1-A1-1






    share|improve this answer




















    • 3





      The deeper answer is that mathematical operations like + and - are defined as an exclusive operation. It's not Excel-specific; the Excel part is the translation from date/timestamps to serial numbers.

      – studog
      Mar 25 at 18:50


















    -1














    I wold like to suggest 3 different Formula to Calculate AGE between 2 Dates.



    enter image description here



    1. To get only Years:

    =INT((B1-A1)/365)



    Or,



    =ROUNDDOWN(YEARFRAC(A1, B1, 1), 0)



    Or, you may use TODAY() Function also:



    =ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)




    1. To Get Complete Age in Years, Months and Days use this one.



      =DATEDIF(A1,B1,"Y") & " Years, " & DATEDIF(A1,B1,"YM") & " Months, " & DATEDIF(A1,B1,"MD") & " Days"


    Edited:



    Counting number of days between two Dates is little bit arbitrary. Basically depends on need.



    1. Count number of days, Excluding Start
      Date
      .

    2. Count number of days, Including Start
      Date
      .

    3. Count number of days, Excluding both
      Start & End Date
      .

    enter image description here



    Considering OP's Sample Dates following Formula can be used.



    Formula for situation 1:



    =DATEDIF(B2,B3,"d")



    =DAYS(B3,B2)



    =INT(B3-B2)



    Formula for situation 2:



    =DATEDIF(B2,B3,"d")+1



    =DAYS(B3,B2)+1



    =INT(B3-B2+1)



    Formula for situation 3:



    =DATEDIF(B2,B3,"d")-1



    =DAYS(B3,B2)-1



    =INT(B3-B2-1)



    Adjust Cell references in the Formula as needed.






    share|improve this answer




















    • 3





      This is over complicating things and doesn't answer the question (how many days). The OP is not asking number of years or years, months and days.

      – Chris Rogers
      Mar 25 at 12:55











    • @ChrisRogers,, Read my post I've suggested all possible Formula, including the OP's one, regarding ((B1-A1)+1)/365.25. It's really unfortunate to get DOWN VOTE after All !!

      – Rajesh S
      Mar 25 at 12:58












    • @ChrisRogers,, this is part of OP,,But often I seen when people are calculating the number of days between 2 dates or age they use the formula B1-A1

      – Rajesh S
      Mar 25 at 13:02











    • @ChrisRogers,, considering the Formula OP has written and I've shown in comments ,, my solution has Focused on ALL that and the Suggested one is an improvise version I've shown with my Answer !! =INT((B1-A1)/365)

      – Rajesh S
      Mar 25 at 13:05







    • 3





      INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.

      – Chris Rogers
      Mar 25 at 13:21











    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "3"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1417263%2fcalculating-the-number-of-days-between-two-dates-in-excel%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    16














    It is not a question of one formula being right and the other being wrong. It is a question of what you are looking for.



    Say you are working on a task 24 hours a day. You begin the task 17 January 2000 at 9:00 AM and complete the task 18 January 2000 at 9:00 AM. If the question is How many days did the task take ? You would take the difference; get 24 hours and answer 1 day.

    If, however, the question is On how many days did you work on the task ? You would immediately respond 2



    Thus =B1 - A1 or =B1 - A1 + 1 might be appropriate depending on what you are trying to measure.






    share|improve this answer





























      16














      It is not a question of one formula being right and the other being wrong. It is a question of what you are looking for.



      Say you are working on a task 24 hours a day. You begin the task 17 January 2000 at 9:00 AM and complete the task 18 January 2000 at 9:00 AM. If the question is How many days did the task take ? You would take the difference; get 24 hours and answer 1 day.

      If, however, the question is On how many days did you work on the task ? You would immediately respond 2



      Thus =B1 - A1 or =B1 - A1 + 1 might be appropriate depending on what you are trying to measure.






      share|improve this answer



























        16












        16








        16







        It is not a question of one formula being right and the other being wrong. It is a question of what you are looking for.



        Say you are working on a task 24 hours a day. You begin the task 17 January 2000 at 9:00 AM and complete the task 18 January 2000 at 9:00 AM. If the question is How many days did the task take ? You would take the difference; get 24 hours and answer 1 day.

        If, however, the question is On how many days did you work on the task ? You would immediately respond 2



        Thus =B1 - A1 or =B1 - A1 + 1 might be appropriate depending on what you are trying to measure.






        share|improve this answer















        It is not a question of one formula being right and the other being wrong. It is a question of what you are looking for.



        Say you are working on a task 24 hours a day. You begin the task 17 January 2000 at 9:00 AM and complete the task 18 January 2000 at 9:00 AM. If the question is How many days did the task take ? You would take the difference; get 24 hours and answer 1 day.

        If, however, the question is On how many days did you work on the task ? You would immediately respond 2



        Thus =B1 - A1 or =B1 - A1 + 1 might be appropriate depending on what you are trying to measure.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Mar 26 at 10:38

























        answered Mar 25 at 13:32









        Gary's StudentGary's Student

        14.2k31733




        14.2k31733























            15














            The answer revolves around how Excel deals with dates. When days between dates are calculated using formulae such as B1-A1, Excel would turn the dates into serial numbers and use the serial numbers to calculate the number or days in between.




            By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. (Source: Microsoft)




            So the days between January 1, 1900 and January 7, 1900 would equal 7 minus 1 equalling 6.



            The same would apply with 1/1/2008 - 7/1/2008



            January 1, 2008 is 39,447 and
            January 7, 2008 is 39,453



            39,453 — 39,447 = 6



            If you wish to count the days inclusive you would need to add 1 to make the formula to be for example B1-A1+1.



            If you want to calculate days exclusive you would need to minus 1 day making B1-A1-1






            share|improve this answer




















            • 3





              The deeper answer is that mathematical operations like + and - are defined as an exclusive operation. It's not Excel-specific; the Excel part is the translation from date/timestamps to serial numbers.

              – studog
              Mar 25 at 18:50















            15














            The answer revolves around how Excel deals with dates. When days between dates are calculated using formulae such as B1-A1, Excel would turn the dates into serial numbers and use the serial numbers to calculate the number or days in between.




            By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. (Source: Microsoft)




            So the days between January 1, 1900 and January 7, 1900 would equal 7 minus 1 equalling 6.



            The same would apply with 1/1/2008 - 7/1/2008



            January 1, 2008 is 39,447 and
            January 7, 2008 is 39,453



            39,453 — 39,447 = 6



            If you wish to count the days inclusive you would need to add 1 to make the formula to be for example B1-A1+1.



            If you want to calculate days exclusive you would need to minus 1 day making B1-A1-1






            share|improve this answer




















            • 3





              The deeper answer is that mathematical operations like + and - are defined as an exclusive operation. It's not Excel-specific; the Excel part is the translation from date/timestamps to serial numbers.

              – studog
              Mar 25 at 18:50













            15












            15








            15







            The answer revolves around how Excel deals with dates. When days between dates are calculated using formulae such as B1-A1, Excel would turn the dates into serial numbers and use the serial numbers to calculate the number or days in between.




            By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. (Source: Microsoft)




            So the days between January 1, 1900 and January 7, 1900 would equal 7 minus 1 equalling 6.



            The same would apply with 1/1/2008 - 7/1/2008



            January 1, 2008 is 39,447 and
            January 7, 2008 is 39,453



            39,453 — 39,447 = 6



            If you wish to count the days inclusive you would need to add 1 to make the formula to be for example B1-A1+1.



            If you want to calculate days exclusive you would need to minus 1 day making B1-A1-1






            share|improve this answer















            The answer revolves around how Excel deals with dates. When days between dates are calculated using formulae such as B1-A1, Excel would turn the dates into serial numbers and use the serial numbers to calculate the number or days in between.




            By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. (Source: Microsoft)




            So the days between January 1, 1900 and January 7, 1900 would equal 7 minus 1 equalling 6.



            The same would apply with 1/1/2008 - 7/1/2008



            January 1, 2008 is 39,447 and
            January 7, 2008 is 39,453



            39,453 — 39,447 = 6



            If you wish to count the days inclusive you would need to add 1 to make the formula to be for example B1-A1+1.



            If you want to calculate days exclusive you would need to minus 1 day making B1-A1-1







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Mar 25 at 12:53

























            answered Mar 25 at 12:47









            Chris RogersChris Rogers

            1,021220




            1,021220







            • 3





              The deeper answer is that mathematical operations like + and - are defined as an exclusive operation. It's not Excel-specific; the Excel part is the translation from date/timestamps to serial numbers.

              – studog
              Mar 25 at 18:50












            • 3





              The deeper answer is that mathematical operations like + and - are defined as an exclusive operation. It's not Excel-specific; the Excel part is the translation from date/timestamps to serial numbers.

              – studog
              Mar 25 at 18:50







            3




            3





            The deeper answer is that mathematical operations like + and - are defined as an exclusive operation. It's not Excel-specific; the Excel part is the translation from date/timestamps to serial numbers.

            – studog
            Mar 25 at 18:50





            The deeper answer is that mathematical operations like + and - are defined as an exclusive operation. It's not Excel-specific; the Excel part is the translation from date/timestamps to serial numbers.

            – studog
            Mar 25 at 18:50











            -1














            I wold like to suggest 3 different Formula to Calculate AGE between 2 Dates.



            enter image description here



            1. To get only Years:

            =INT((B1-A1)/365)



            Or,



            =ROUNDDOWN(YEARFRAC(A1, B1, 1), 0)



            Or, you may use TODAY() Function also:



            =ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)




            1. To Get Complete Age in Years, Months and Days use this one.



              =DATEDIF(A1,B1,"Y") & " Years, " & DATEDIF(A1,B1,"YM") & " Months, " & DATEDIF(A1,B1,"MD") & " Days"


            Edited:



            Counting number of days between two Dates is little bit arbitrary. Basically depends on need.



            1. Count number of days, Excluding Start
              Date
              .

            2. Count number of days, Including Start
              Date
              .

            3. Count number of days, Excluding both
              Start & End Date
              .

            enter image description here



            Considering OP's Sample Dates following Formula can be used.



            Formula for situation 1:



            =DATEDIF(B2,B3,"d")



            =DAYS(B3,B2)



            =INT(B3-B2)



            Formula for situation 2:



            =DATEDIF(B2,B3,"d")+1



            =DAYS(B3,B2)+1



            =INT(B3-B2+1)



            Formula for situation 3:



            =DATEDIF(B2,B3,"d")-1



            =DAYS(B3,B2)-1



            =INT(B3-B2-1)



            Adjust Cell references in the Formula as needed.






            share|improve this answer




















            • 3





              This is over complicating things and doesn't answer the question (how many days). The OP is not asking number of years or years, months and days.

              – Chris Rogers
              Mar 25 at 12:55











            • @ChrisRogers,, Read my post I've suggested all possible Formula, including the OP's one, regarding ((B1-A1)+1)/365.25. It's really unfortunate to get DOWN VOTE after All !!

              – Rajesh S
              Mar 25 at 12:58












            • @ChrisRogers,, this is part of OP,,But often I seen when people are calculating the number of days between 2 dates or age they use the formula B1-A1

              – Rajesh S
              Mar 25 at 13:02











            • @ChrisRogers,, considering the Formula OP has written and I've shown in comments ,, my solution has Focused on ALL that and the Suggested one is an improvise version I've shown with my Answer !! =INT((B1-A1)/365)

              – Rajesh S
              Mar 25 at 13:05







            • 3





              INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.

              – Chris Rogers
              Mar 25 at 13:21















            -1














            I wold like to suggest 3 different Formula to Calculate AGE between 2 Dates.



            enter image description here



            1. To get only Years:

            =INT((B1-A1)/365)



            Or,



            =ROUNDDOWN(YEARFRAC(A1, B1, 1), 0)



            Or, you may use TODAY() Function also:



            =ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)




            1. To Get Complete Age in Years, Months and Days use this one.



              =DATEDIF(A1,B1,"Y") & " Years, " & DATEDIF(A1,B1,"YM") & " Months, " & DATEDIF(A1,B1,"MD") & " Days"


            Edited:



            Counting number of days between two Dates is little bit arbitrary. Basically depends on need.



            1. Count number of days, Excluding Start
              Date
              .

            2. Count number of days, Including Start
              Date
              .

            3. Count number of days, Excluding both
              Start & End Date
              .

            enter image description here



            Considering OP's Sample Dates following Formula can be used.



            Formula for situation 1:



            =DATEDIF(B2,B3,"d")



            =DAYS(B3,B2)



            =INT(B3-B2)



            Formula for situation 2:



            =DATEDIF(B2,B3,"d")+1



            =DAYS(B3,B2)+1



            =INT(B3-B2+1)



            Formula for situation 3:



            =DATEDIF(B2,B3,"d")-1



            =DAYS(B3,B2)-1



            =INT(B3-B2-1)



            Adjust Cell references in the Formula as needed.






            share|improve this answer




















            • 3





              This is over complicating things and doesn't answer the question (how many days). The OP is not asking number of years or years, months and days.

              – Chris Rogers
              Mar 25 at 12:55











            • @ChrisRogers,, Read my post I've suggested all possible Formula, including the OP's one, regarding ((B1-A1)+1)/365.25. It's really unfortunate to get DOWN VOTE after All !!

              – Rajesh S
              Mar 25 at 12:58












            • @ChrisRogers,, this is part of OP,,But often I seen when people are calculating the number of days between 2 dates or age they use the formula B1-A1

              – Rajesh S
              Mar 25 at 13:02











            • @ChrisRogers,, considering the Formula OP has written and I've shown in comments ,, my solution has Focused on ALL that and the Suggested one is an improvise version I've shown with my Answer !! =INT((B1-A1)/365)

              – Rajesh S
              Mar 25 at 13:05







            • 3





              INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.

              – Chris Rogers
              Mar 25 at 13:21













            -1












            -1








            -1







            I wold like to suggest 3 different Formula to Calculate AGE between 2 Dates.



            enter image description here



            1. To get only Years:

            =INT((B1-A1)/365)



            Or,



            =ROUNDDOWN(YEARFRAC(A1, B1, 1), 0)



            Or, you may use TODAY() Function also:



            =ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)




            1. To Get Complete Age in Years, Months and Days use this one.



              =DATEDIF(A1,B1,"Y") & " Years, " & DATEDIF(A1,B1,"YM") & " Months, " & DATEDIF(A1,B1,"MD") & " Days"


            Edited:



            Counting number of days between two Dates is little bit arbitrary. Basically depends on need.



            1. Count number of days, Excluding Start
              Date
              .

            2. Count number of days, Including Start
              Date
              .

            3. Count number of days, Excluding both
              Start & End Date
              .

            enter image description here



            Considering OP's Sample Dates following Formula can be used.



            Formula for situation 1:



            =DATEDIF(B2,B3,"d")



            =DAYS(B3,B2)



            =INT(B3-B2)



            Formula for situation 2:



            =DATEDIF(B2,B3,"d")+1



            =DAYS(B3,B2)+1



            =INT(B3-B2+1)



            Formula for situation 3:



            =DATEDIF(B2,B3,"d")-1



            =DAYS(B3,B2)-1



            =INT(B3-B2-1)



            Adjust Cell references in the Formula as needed.






            share|improve this answer















            I wold like to suggest 3 different Formula to Calculate AGE between 2 Dates.



            enter image description here



            1. To get only Years:

            =INT((B1-A1)/365)



            Or,



            =ROUNDDOWN(YEARFRAC(A1, B1, 1), 0)



            Or, you may use TODAY() Function also:



            =ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)




            1. To Get Complete Age in Years, Months and Days use this one.



              =DATEDIF(A1,B1,"Y") & " Years, " & DATEDIF(A1,B1,"YM") & " Months, " & DATEDIF(A1,B1,"MD") & " Days"


            Edited:



            Counting number of days between two Dates is little bit arbitrary. Basically depends on need.



            1. Count number of days, Excluding Start
              Date
              .

            2. Count number of days, Including Start
              Date
              .

            3. Count number of days, Excluding both
              Start & End Date
              .

            enter image description here



            Considering OP's Sample Dates following Formula can be used.



            Formula for situation 1:



            =DATEDIF(B2,B3,"d")



            =DAYS(B3,B2)



            =INT(B3-B2)



            Formula for situation 2:



            =DATEDIF(B2,B3,"d")+1



            =DAYS(B3,B2)+1



            =INT(B3-B2+1)



            Formula for situation 3:



            =DATEDIF(B2,B3,"d")-1



            =DAYS(B3,B2)-1



            =INT(B3-B2-1)



            Adjust Cell references in the Formula as needed.







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Mar 26 at 10:11

























            answered Mar 25 at 12:45









            Rajesh SRajesh S

            4,3652624




            4,3652624







            • 3





              This is over complicating things and doesn't answer the question (how many days). The OP is not asking number of years or years, months and days.

              – Chris Rogers
              Mar 25 at 12:55











            • @ChrisRogers,, Read my post I've suggested all possible Formula, including the OP's one, regarding ((B1-A1)+1)/365.25. It's really unfortunate to get DOWN VOTE after All !!

              – Rajesh S
              Mar 25 at 12:58












            • @ChrisRogers,, this is part of OP,,But often I seen when people are calculating the number of days between 2 dates or age they use the formula B1-A1

              – Rajesh S
              Mar 25 at 13:02











            • @ChrisRogers,, considering the Formula OP has written and I've shown in comments ,, my solution has Focused on ALL that and the Suggested one is an improvise version I've shown with my Answer !! =INT((B1-A1)/365)

              – Rajesh S
              Mar 25 at 13:05







            • 3





              INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.

              – Chris Rogers
              Mar 25 at 13:21












            • 3





              This is over complicating things and doesn't answer the question (how many days). The OP is not asking number of years or years, months and days.

              – Chris Rogers
              Mar 25 at 12:55











            • @ChrisRogers,, Read my post I've suggested all possible Formula, including the OP's one, regarding ((B1-A1)+1)/365.25. It's really unfortunate to get DOWN VOTE after All !!

              – Rajesh S
              Mar 25 at 12:58












            • @ChrisRogers,, this is part of OP,,But often I seen when people are calculating the number of days between 2 dates or age they use the formula B1-A1

              – Rajesh S
              Mar 25 at 13:02











            • @ChrisRogers,, considering the Formula OP has written and I've shown in comments ,, my solution has Focused on ALL that and the Suggested one is an improvise version I've shown with my Answer !! =INT((B1-A1)/365)

              – Rajesh S
              Mar 25 at 13:05







            • 3





              INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.

              – Chris Rogers
              Mar 25 at 13:21







            3




            3





            This is over complicating things and doesn't answer the question (how many days). The OP is not asking number of years or years, months and days.

            – Chris Rogers
            Mar 25 at 12:55





            This is over complicating things and doesn't answer the question (how many days). The OP is not asking number of years or years, months and days.

            – Chris Rogers
            Mar 25 at 12:55













            @ChrisRogers,, Read my post I've suggested all possible Formula, including the OP's one, regarding ((B1-A1)+1)/365.25. It's really unfortunate to get DOWN VOTE after All !!

            – Rajesh S
            Mar 25 at 12:58






            @ChrisRogers,, Read my post I've suggested all possible Formula, including the OP's one, regarding ((B1-A1)+1)/365.25. It's really unfortunate to get DOWN VOTE after All !!

            – Rajesh S
            Mar 25 at 12:58














            @ChrisRogers,, this is part of OP,,But often I seen when people are calculating the number of days between 2 dates or age they use the formula B1-A1

            – Rajesh S
            Mar 25 at 13:02





            @ChrisRogers,, this is part of OP,,But often I seen when people are calculating the number of days between 2 dates or age they use the formula B1-A1

            – Rajesh S
            Mar 25 at 13:02













            @ChrisRogers,, considering the Formula OP has written and I've shown in comments ,, my solution has Focused on ALL that and the Suggested one is an improvise version I've shown with my Answer !! =INT((B1-A1)/365)

            – Rajesh S
            Mar 25 at 13:05






            @ChrisRogers,, considering the Formula OP has written and I've shown in comments ,, my solution has Focused on ALL that and the Suggested one is an improvise version I've shown with my Answer !! =INT((B1-A1)/365)

            – Rajesh S
            Mar 25 at 13:05





            3




            3





            INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.

            – Chris Rogers
            Mar 25 at 13:21





            INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.

            – Chris Rogers
            Mar 25 at 13:21

















            draft saved

            draft discarded
















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1417263%2fcalculating-the-number-of-days-between-two-dates-in-excel%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Adding axes to figuresAdding axes labels to LaTeX figuresLaTeX equivalent of ConTeXt buffersRotate a node but not its content: the case of the ellipse decorationHow to define the default vertical distance between nodes?TikZ scaling graphic and adjust node position and keep font sizeNumerical conditional within tikz keys?adding axes to shapesAlign axes across subfiguresAdding figures with a certain orderLine up nested tikz enviroments or how to get rid of themAdding axes labels to LaTeX figures

            Tähtien Talli Jäsenet | Lähteet | NavigointivalikkoSuomen Hippos – Tähtien Talli

            Do these cracks on my tires look bad? The Next CEO of Stack OverflowDry rot tire should I replace?Having to replace tiresFishtailed so easily? Bad tires? ABS?Filling the tires with something other than air, to avoid puncture hassles?Used Michelin tires safe to install?Do these tyre cracks necessitate replacement?Rumbling noise: tires or mechanicalIs it possible to fix noisy feathered tires?Are bad winter tires still better than summer tires in winter?Torque converter failure - Related to replacing only 2 tires?Why use snow tires on all 4 wheels on 2-wheel-drive cars?