Calculating the number of days between two dates in ExcelGetting Networkdays to accurately reflect the number...
Why are on-board computers allowed to change controls without notifying the pilots?
Is there a good way to store credentials outside of a password manager?
Is exact Kanji stroke length important?
How will losing mobility of one hand affect my career as a programmer?
Implement the Thanos sorting algorithm
Using parameter substitution on a Bash array
How do I keep an essay about "feeling flat" from feeling flat?
What is difference between behavior and behaviour
Hide Select Output from T-SQL
What would happen if the UK refused to take part in EU Parliamentary elections?
Is the destination of a commercial flight important for the pilot?
Curses work by shouting - How to avoid collateral damage?
Why "be dealt cards" rather than "be dealing cards"?
Everything Bob says is false. How does he get people to trust him?
How does it work when somebody invests in my business?
How can I replace every global instance of "x[2]" with "x_2"
Is there any reason not to eat food that's been dropped on the surface of the moon?
Can criminal fraud exist without damages?
Do I need a multiple entry visa for a trip UK -> Sweden -> UK?
How do I define a right arrow with bar in LaTeX?
What would be the benefits of having both a state and local currencies?
Efficiently merge handle parallel feature branches in SFDX
Valid Badminton Score?
Do there exist finite commutative rings with identity that are not Bézout rings?
Calculating the number of days between two dates in Excel
Getting 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?Determine days in Excel formulaCondition formatting dates two week before the actual target dateHow do you format an Excel date to represent >31 days with hours, minutes and secondsCalculating the average Interest Rate using a range of date inputs in excelExcel: Count distinct days of datetimesHow to find the max value between two dates in ExcelCalculate date when age plus service equals specific value 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?
microsoft-excel date-time date
edited 19 mins ago
Scott
16.1k113990
asked yesterday
NazNaz
17412
add a comment |
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
edited 19 mins ago
Scott
16.1k113990
asked yesterday
NazNaz
17412
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
yesterday
@slhck That really makes sense when you think about the time as well as date.
– Naz
yesterday
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
yesterday
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
yesterday
(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, ifB1= 27 March 1979 andA1= 27 March 1959, what does your formula say? What does it say forB1= 26 March 1979?
– Scott
14 mins ago
add a comment |
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
edited 19 mins ago
Scott
16.1k113990
asked yesterday
NazNaz
17412
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
microsoft-excel date-time date
edited 19 mins ago
Scott
16.1k113990
asked yesterday
NazNaz
17412
edited 19 mins ago
Scott
16.1k113990
asked yesterday
NazNaz
17412
edited 19 mins ago
Scott
16.1k113990
edited 19 mins ago
Scott
16.1k113990
edited 19 mins ago
Scott
16.1k113990
16.1k113990
asked yesterday
NazNaz
17412
asked yesterday
NazNaz
17412
asked yesterday
NazNaz
17412
17412
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
yesterday
@slhck That really makes sense when you think about the time as well as date.
– Naz
yesterday
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
yesterday
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
yesterday
(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, ifB1= 27 March 1979 andA1= 27 March 1959, what does your formula say? What does it say forB1= 26 March 1979?
– Scott
14 mins ago
add a comment |
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
yesterday
@slhck That really makes sense when you think about the time as well as date.
– Naz
yesterday
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
yesterday
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
yesterday
(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, ifB1= 27 March 1979 andA1= 27 March 1959, what does your formula say? What does it say forB1= 26 March 1979?
– Scott
14 mins ago
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
yesterday
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
yesterday
@slhck That really makes sense when you think about the time as well as date.
– Naz
yesterday
@slhck That really makes sense when you think about the time as well as date.
– Naz
yesterday
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
yesterday
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
yesterday
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
yesterday
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
yesterday
(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
14 mins ago
(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
14 mins ago
add a comment |
3 Answers
3
active
oldest
votes
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
answered yesterday
Chris RogersChris Rogers
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
yesterday
add a comment |
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.
answered yesterday
Gary's StudentGary's Student
14.1k31733
add a comment |
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.
answered yesterday
Rajesh SRajesh S
4,2892624
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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
2
INT((B1-A1)/365)can provide the wrong result if there is are leap years involved.
– Chris Rogers
yesterday
|
show 2 more comments
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
answered yesterday
Chris RogersChris Rogers
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
yesterday
add a comment |
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
answered yesterday
Chris RogersChris Rogers
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
yesterday
add a comment |
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
answered yesterday
Chris RogersChris Rogers
1,021220
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
answered yesterday
Chris RogersChris Rogers
1,021220
edited yesterday
answered yesterday
Chris RogersChris Rogers
1,021220
answered yesterday
Chris RogersChris Rogers
1,021220
answered yesterday
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
yesterday
add a comment |
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
yesterday
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
yesterday
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
yesterday
add a comment |
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.
answered yesterday
Gary's StudentGary's Student
14.1k31733
add a comment |
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.
answered yesterday
Gary's StudentGary's Student
14.1k31733
add a comment |
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.
answered yesterday
Gary's StudentGary's Student
14.1k31733
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.
answered yesterday
Gary's StudentGary's Student
14.1k31733
edited 18 hours ago
answered yesterday
Gary's StudentGary's Student
14.1k31733
answered yesterday
Gary's StudentGary's Student
14.1k31733
answered yesterday
Gary's StudentGary's Student
14.1k31733
14.1k31733
add a comment |
add a comment |
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.
answered yesterday
Rajesh SRajesh S
4,2892624
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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
2
INT((B1-A1)/365)can provide the wrong result if there is are leap years involved.
– Chris Rogers
yesterday
|
show 2 more comments
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.
answered yesterday
Rajesh SRajesh S
4,2892624
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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
2
INT((B1-A1)/365)can provide the wrong result if there is are leap years involved.
– Chris Rogers
yesterday
|
show 2 more comments
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.
answered yesterday
Rajesh SRajesh S
4,2892624
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.
answered yesterday
Rajesh SRajesh S
4,2892624
edited 19 hours ago
answered yesterday
Rajesh SRajesh S
4,2892624
answered yesterday
Rajesh SRajesh S
4,2892624
answered yesterday
Rajesh SRajesh S
4,2892624
4,2892624
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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
2
INT((B1-A1)/365)can provide the wrong result if there is are leap years involved.
– Chris Rogers
yesterday
|
show 2 more comments
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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
2
INT((B1-A1)/365)can provide the wrong result if there is are leap years involved.
– Chris Rogers
yesterday
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
yesterday
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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
@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
yesterday
2
2
INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.– Chris Rogers
yesterday
INT((B1-A1)/365) can provide the wrong result if there is are leap years involved.– Chris Rogers
yesterday
|
show 2 more comments
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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
yesterday
@slhck That really makes sense when you think about the time as well as date.
– Naz
yesterday
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
yesterday
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
yesterday
(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 andA1= 27 March 1959, what does your formula say? What does it say forB1= 26 March 1979?– Scott
14 mins ago