Hello excel experts...........
I have a date (A1) 01-01-2010 (B1) 31-03-2010
I put a formula for Years =DATEDIF(A1,B1,"Y")
gives 0 Years
I put a formula for Months =DATEDIF(A1,B1,"Ym")
gives 02 Months
I put a formula for Days =day(DATEDIF(A1,B1,"MD")+1)
gives 31 Days
but problem is it gives 31 days, but i want it shows 0days and Plus +1 in Month, should be 3
The final result should be 0Years 03Months and 0Days
how to calculate
Last edited by AVG123; 01-02-2018 at 05:59 AM.
Try this:
=DATEDIF(A1,B1+1,"Ym")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Give this try
Formula:
Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Thanks for reply it is very simple.....what to do if
DOB is (A2) 18-01-1965,
DOA is (B2)15-04-1989
DOR is (C2)14-11-2016
The formula i have used DATEDIF(C2,D2,"y")&"Yr "&DATEDIF(C2,D2,"YM")&"Mn "&DAY(DATEDIF(C2,D2,"MD"))+1&"Dys" it gives result below
27Yr 06Mn 31Dy
but final result should be 27Yr 07Mn 0Dy
how to solve this
This:
=DATEDIF(C1,D1,"y")&"Yr "&DATEDIF(C1,D1+1,"YM")&"Mn "&DAY(DATEDIF(C1,D1+1,"MD"))+1&"Dys"
The problem here is that your own calculations are incorrect, so however you try to 'solve' this, you are going to have 'errors' occurring at some point. You would be much better accepting that 01-01-2010 to 31-03-2010 is in fact 2 months and 31 days, NOT 3 months.
Last edited by AliGW; 01-02-2018 at 05:35 AM.
Thankyou but i want when it shows 31days, 31 days itself a month.........so increment of 1 in the Month and days should be 0 or blank
No, you are incorrect.
01-01-2010 to 01-03-2010 is exactly 3 months. You have to see these not just as dates, but times, too.
01-01-2010 00:00:00 to 31-10-2010 23.59.59 is one second less than three months.
Thanks dear but what to do
if
DOB is (A2) 18-01-1965,
DOA is (B2)15-04-1989
DOR is (C2)14-11-2016
The formula i have used DATEDIF(C2,D2,"y")&"Yr "&DATEDIF(C2,D2,"YM")&"Mn "&DAY(DATEDIF(C2,D2,"MD"))+1&"Dys" it gives result below
27Yr 06Mn 31Dy
but final result should be 27Yr 07Mn 0Dy
how to solve this
No, it should not! Your calculations are incorrect, for the reasons I gave in my last post. To get the results you want, you need to change the second date in the formula.
I have already shown you what to do to generate the incorrect outcome you want and I have also pointed out that this will cause issues for you at some point down the line (see post #5).
Last edited by AliGW; 01-02-2018 at 05:49 AM.
01-01-2010 00:00:00 to 31-10-2010 23.59.59 is one second less than three months. i think some mistake here.......
01-01-2010 00:00:00 to 31-01-2010=1 month
01-02-2010 00:00:00 to 28-02-2010=1 month
01-03-2010 00:00:00 to 31-03-2010=1 month
Upload some more example with expected result.
No - you are absolutely incorrect. The latest time that the last day of the month can represent is a fraction of a second before midnight. Without any timestamp added, 31-03-2010 represents 31-03-2010 00:00:00, which is 23 hours and 59 minutes short of a full day.Originally Posted by gyan123
Last edited by AliGW; 01-02-2018 at 05:55 AM.
Look at it this way: if you set up your bank account to make payments (let's say your gas bill) every 3 months, those payments will go out, for example, on 01-01-2018, 01-04-2018, 01-07-2018 and 01-10-2018.
I uploaded the softfile please see
Try
B11Formula:Please Login or Register to view this content.
Row\Col 27Yr 7Mn 1Dys 27Yr 6Mn 31Dys Final Answer should be 27Years 7Months and 0Days
No, you are incorrect (see post #13). To arrive at your desired result of exactly 27 years and 7 months, the end date must be set at 15/11/2016.
Excel is not wrong - you need to accept the logic and work with it.
EDIT: Shukla's formula will do what you want, but only because you want it that way, not because the answer is correct.
Last edited by AliGW; 01-02-2018 at 06:05 AM.
Ali actually he is correct i google it and the result is in the below snapshot
No, he is not correct - look at the tick box in your screenshot!!! What does it say? I have explained the logic in posts #7, #12 and #13.
Yes your logic is absolutely correct but he wants to include last working day as well as.
Yes, I know that HE wants to include that day, but he is saying that Excel is calculating it incorrectly, and Excel is NOT calculating it incorrectly. What he wants and what he is saying are two different things. I am trying to explain why Excel is calculating the date difference in the way it does. It can be overcome in several ways: I would have a hidden cell with the end date plus one day and use that in my DATEDIF calculation rather than trying to 'fudge' the formula to do something it's not designed to do.
PS It's not really MY logic, it's EXCEL's logic, and it is mathematically correct.
Last edited by AliGW; 01-02-2018 at 06:18 AM.
@Gyan - do you understand what I have tried to explain to you? If you want help with my suggestion of a hidden cell, let me know.
The DAY function here is unnecessary. If you want to count end date just add 1 to D2 in all 3 DATEDIF functions (as Ali suggests) but lose DAY function, e.g.
=DATEDIF(C2,D2+1,"y")&"Yr "&DATEDIF(C2,D2+1,"YM")&"Mn "&DATEDIF(C2,D2+1,"MD")&"Dys"
Hello shukla,
I see this working for the specific example but, in general, this formula isn't accurate. If A2 is 1-Jan-2018 and B2 is 1-Feb-2018 then your formula returns 0Years 1Months and 4Days!
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks