Issue with calculating date/age in Excel

Answers

First considering the Formula you have used to Calculate Year between two Dates.

=(C4-B4)/365.2

Actually it should written like this:

=INT((C4-B4)/365)

Considering First two dates 05/04/18 -18/09/17 the Formula returns 0.

You may use this Formula also:

=ROUNDDOWN(YEARFRAC(B4,C4,1),0)& " Year"

If you want to illustrate complete picture like difference between two Dates as Year, Months & Days then use this one.

=DATEDIF(B4,C4,"Y") & " Years, " & DATEDIF(B4,C4,"YM") & " Months, " & DATEDIF(B4,C4,"MD") & " Days"

Note: Format for Dates in both Columns is, DD/MM/YY.

N.B.

Let's consider the DATEDIF mechanism.

=DATEDIF (start_date, end_date, unit)

Considering your Formula the Start Date is in B4 and End Date is in C4
The unity "Y" is for Years, counts number of complete Years, between Start & End Dates.
"YM" Counts Months excluding days and Years, the Date difference in months, ignoring days and Years.
"MD" counts Days excluding Years and Months, the Date difference in days, ignoring Months and Years.
Format the 3rd Column as GENERAL before you write any of these Formulas.
Adjust cell references in the Formula as needed.

Rajesh S

Posted 2019-02-27T14:09:32.313

Reputation: 6 800

4=DATEDIF()? I've just opened a blank Excel, put two random dates in, used =DATEDIF(A1,B1,"Y") and it's given me the number of years between the two dates... Can you provide a format you're expecting? Number of days between the dates, number of years, number of months.... certain functions can do what you're asking, some have limitations... – Kinnectus – 2019-02-27T14:16:35.287

2Hello and welcome to this site. Unfortunately your question does not supply any useful information nor does your screenshot supply the required information. Please EDIT your question to include some sample data, what you want and what you have tried. Be as specific as possible. – Kevin Anthony Oppegaard Rose – 2019-02-27T14:17:12.633

Thank you. Sorry for the trouble. I've also just opened a new fresh document and inputed the dates and it is working fine... The document was sent to me by my supervisor for a research project (I don't want to breech confidentiality) but I guess that it's got something to do with the formatting. It still generates me DD/MM/YYYY. I might just redo the document. – Johnny – 2019-02-27T14:21:41.773

If you edit your question to include the information asked for, and an accurate description of the results you're seeing in one place vs the other, I'm certain someone can help you understand what's going on. – Alex M – 2019-02-27T23:23:47.943

You have two problems but we'll need specifics to be able to help. Dates are stored as day counts since January 0, 1900. If you format the difference between dates as a date, you'll get results like a 10 day difference appearing as Jan 10, 1900 since that was day 10. You need to format the result to display in the desired form. That said, the dates showing as results are wrong, so there's a problem with your formula (should require only simple subtraction). Please edit your question to include the sample data that produced these results and the formula you used. – fixer1234 – 2019-02-27T23:58:20.543

Hello, I've updated my question with 2 screenshots. I've added a few more samples of data. Hopefully the question is better :( – Johnny – 2019-02-28T03:13:59.593