Calculating the number of days between two dates in Excel

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?

Naz

Posted 2019-03-25T12:12:38.563

Reputation: 299

27 - 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 – 2019-03-25T12:38:42.640

@slhck That really makes sense when you think about the time as well as date. – Naz – 2019-03-25T12:54:00.757

7If 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 – 2019-03-25T13:28:28.387

2You'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 – 2019-03-25T18:56:32.783

(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 – 2019-03-27T05:14:55.970

Answers

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.

Gary's Student

Posted 2019-03-25T12:12:38.563

Reputation: 15 540

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

Chris Rogers

Posted 2019-03-25T12:12:38.563

Reputation: 1 153

3The 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 – 2019-03-25T18:50:19.030

-1

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

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)

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.

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

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.

Rajesh S

Posted 2019-03-25T12:12:38.563

Reputation: 6 800

3This 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 – 2019-03-25T12:55:30.957

@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 – 2019-03-25T12:58:35.843

@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 – 2019-03-25T13:02:30.007

@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 – 2019-03-25T13:05:20.733

@ChrisRogers,, this =((B1-A1)+1)/365.25 returns 18.22861054 and even this =INT((B139-A139)+1)/365.25 returns the same stuff 18.22861054, so that I've suggested the Correct Version is ,,, =INT((B1-A1)/365) – Rajesh S – 2019-03-25T13:12:40.137

3INT((B1-A1)/365) can provide the wrong result if there is are leap years involved. – Chris Rogers – 2019-03-25T13:21:11.090

As a coder who regularly has to cope with dates and timezones: Please test these functions whether they are correct in all circumstances. For example, calculating the number of days between 2011-12-20 and 2012-01-01 should give different results when the timezone of these dates is Pacific/Apia in contrast to any other timezone. (Samoa skipped December, 30th 2011; ref: https://en.wikipedia.org/wiki/Time_in_Samoa)

– orithena – 2019-03-26T15:33:05.270