• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Datedif Function

M

mithil1

Member
When I use =DATEDIF(Cell,0) I am getting the month with rouding off. For e.g. if its 45 days then I get 1 month instead of 1.5 or 1.3 etc. Further, the days are less than 30, I get 0 in months.

Question : How can I get the exact months without rounding off ? and How can I get months for less than 30 days displayed either as 0.25 or 0.50 etc.

Thanks
 
Hi ,

If your definition of a month is always 30 days , then why can't you just divide by 30 to get the number of months ?

Thus 45 divided by 30 will give you 1.5 ; 39 divided by 30 will give you 1.3

The same holds true for the number of days which are less than 30 ; 20 days is equivalent to 20/30 = 0.6666 months.

Narayan
 
NARAYANK991 said:
Hi ,

If your definition of a month is always 30 days , then why can't you just divide by 30 to get the number of months ?

Thus 45 divided by 30 will give you 1.5 ; 39 divided by 30 will give you 1.3

The same holds true for the number of days which are less than 30 ; 20 days is equivalent to 20/30 = 0.6666 months.

Narayan
Click to expand...
Thanks Narayan. Don't know why I didn't this before. Its simple. Probably, back then I was thinking DatedIF is pulling it off of a certain month.

Mithil
 
mithil1 said:
One of the person suggested in the same thread to divide it by 30.
Click to expand...
Hi, mithil1!

I think that if you do that you won't be retrieving the exact decimal part since not all months have 30 days. Instead of that you can do this, assuming that starting date is in A1 and ending date in B1:

a) separate cells

C1: no. of entire months
=(AÑO(B1)-AÑO(A1))*12+MES(B1)-MES(A1)-(DIA(B1)<DIA(A1)) -----> in English: =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))

D1: no. of remaining days
=RESIDUO(DIA(B1)-DIA(A1)+DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1));DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1))) -----> in English: =MOD(DAY(B1)-DAY(A1)+DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)),DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)))

E1: final result
=C1+D1/DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)) -----> in English: =C1+D1/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))

b) unique cell

F1: final result
=(AÑO(B1)-AÑO(A1))*12+MES(B1)-MES(A1)-(DIA(B1)<DIA(A1))+RESIDUO(DIA(B1)-DIA(A1)+DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1));DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)))/DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)) -----> in English: =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))+MOD(DAY(B1)-DAY(A1)+DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)),DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)))/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))

A bit weird, isn't it? Well, it's not my fault, you've asked the question, now it's you who decide which accuracy you want to achieve... otherwise, talk to Redmond guys.

Hope it helps.

Regards!
 
Thanks, I'll try!


SirJB7 said:
Hi, mithil1!

I think that if you do that you won't be retrieving the exact decimal part since not all months have 30 days. Instead of that you can do this, assuming that starting date is in A1 and ending date in B1:

a) separate cells

C1: no. of entire months
=(AÑO(B1)-AÑO(A1))*12+MES(B1)-MES(A1)-(DIA(B1)<DIA(A1)) -----> in English: =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))

D1: no. of remaining days
=RESIDUO(DIA(B1)-DIA(A1)+DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1));DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1))) -----> in English: =MOD(DAY(B1)-DAY(A1)+DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)),DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)))

E1: final result
=C1+D1/DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)) -----> in English: =C1+D1/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))

b) unique cell

F1: final result
=(AÑO(B1)-AÑO(A1))*12+MES(B1)-MES(A1)-(DIA(B1)<DIA(A1))+RESIDUO(DIA(B1)-DIA(A1)+DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1));DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)))/DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)) -----> in English: =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))+MOD(DAY(B1)-DAY(A1)+DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)),DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)))/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))

A bit weird, isn't it? Well, it's not my fault, you've asked the question, now it's you who decide which accuracy you want to achieve... otherwise, talk to Redmond guys.

Hope it helps.

Regards!
Click to expand...
 
This didn't work out because in column E I am getting #######



SirJB7 said:
Hi, mithil1!

I think that if you do that you won't be retrieving the exact decimal part since not all months have 30 days. Instead of that you can do this, assuming that starting date is in A1 and ending date in B1:

a) separate cells

C1: no. of entire months
=(AÑO(B1)-AÑO(A1))*12+MES(B1)-MES(A1)-(DIA(B1)<DIA(A1)) -----> in English: =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))

D1: no. of remaining days
=RESIDUO(DIA(B1)-DIA(A1)+DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1));DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1))) -----> in English: =MOD(DAY(B1)-DAY(A1)+DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)),DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)))

E1: final result
=C1+D1/DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)) -----> in English: =C1+D1/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))

b) unique cell

F1: final result
=(AÑO(B1)-AÑO(A1))*12+MES(B1)-MES(A1)-(DIA(B1)<DIA(A1))+RESIDUO(DIA(B1)-DIA(A1)+DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1));DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)))/DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)) -----> in English: =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))+MOD(DAY(B1)-DAY(A1)+DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)),DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)))/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))

A bit weird, isn't it? Well, it's not my fault, you've asked the question, now it's you who decide which accuracy you want to achieve... otherwise, talk to Redmond guys.

Hope it helps.

Regards!
Click to expand...
 
Hi, mithil!
Give a look at the uploaded file. There you'll find a working model with all the posted formulas.
Regards!
 

Attachments

  • Libro1.xlsx
    48.1 KB · Views: 4
SirJB7 said:
Hi, mithil!
Give a look at the uploaded file. There you'll find a working model with all the posted formulas.
Regards!
Click to expand...
Thanks Sir JB7. I really appreciate that you took some time to work on this. Since this is nested formula, I always get confused how to interpret them. Could you please advice me on how to do so in future I can do it on my own. Also, why do we first multiply by 12 ? I am using evaluate formula option but I still don't understand
 
Hi, mithil1!

It's easier to understand if you go thru a) first. Then b) is just replacing values in a unique last formula.
SirJB7 said:
a) separate cells

C1: no. of entire months
=(AÑO(B1)-AÑO(A1))*12+MES(B1)-MES(A1)-(DIA(B1)<DIA(A1)) -----> in English: =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))

D1: no. of remaining days
=RESIDUO(DIA(B1)-DIA(A1)+DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1));DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1))) -----> in English: =MOD(DAY(B1)-DAY(A1)+DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)),DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)))

E1: final result
=C1+D1/DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)) -----> in English: =C1+D1/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))

b) unique cell

F1: final result
=(AÑO(B1)-AÑO(A1))*12+MES(B1)-MES(A1)-(DIA(B1)<DIA(A1))+RESIDUO(DIA(B1)-DIA(A1)+DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1));DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)))/DIA(FIN.MES(FECHA(AÑO(A1);MES(A1);0);1)) -----> in English: =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))+MOD(DAY(B1)-DAY(A1)+DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)),DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1)))/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))
Click to expand...
C1 holds the no. of whole months. Used to calculate the integer part of the result. It has 3 components:
- difference of years, multiplied by 12
- difference of months
- adjustment for last month if day 1 is greater than day 2

D1 holds the remaining days. Used to calculate the decimal part of the result. Only one term:
- take the difference between days of both dates, DAY(B1)-DAY(A1)
- add the module used next, no. of days of month of starting date, DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),0),1))
- calculate module, MOD(difference of days + module,module)

E1 holds the sum of integer and decimal parts, C1+D1

F1 holds the sum of integer and decimal parts, but replacing C1 and D1 by formulas containing only reference to the original dates (A1 & B1) without using helper columns (C1 & D1).

Regards!
 
I tried your formula its not working out. e.g. date 08/01/2016 to 6/30/2017. Its 11 months exact, where as, your formula is giving 10.93

Thanks
 
I agree but that's not the correct way to do. We have to count full month of August and June. If it were 2nd aug or 4th august the what you are saying would stand true.
 
Hi ,

The whole point of the link that I posted is that there is no way a formula will ever give an accurate result for the difference between two dates in months.

That is because we can easily calculate the difference between two dates in days , since that is just the later date - the earlier date + 1.

How this difference in days translates into months depends on the months that are in between ; thus a difference of 62 days in December and January will be exactly 2 months , since both months have 31 days each ; the same 62 days in February , March and a few days in April will translate into 2 months and a fraction.

There is no formula which will do this considering the months which fall in between the two dates.

You will do better to understand this difficulty and use a standard definition of a month as 30 days or 365/12 days or do away altogether , with trying to translate the difference between two dates in months.

Narayan
 
mithil1 said:
I agree but that's not the correct way to do. We have to count full month of August and June. If it were 2nd aug or 4th august the what you are saying would stand true.
Click to expand...
Hi, mithil1!
I don't agree.
From Aug 1st to Sep 1st, 1 month; to Oct 1st, 2 months, ... and so on... to June 1st, 10 months; the 11th month will happen on July 1st. Unless maths have changed lately.
Regards!
 
Hi ,

I think there is still some confusion over what exactly we are discussing !

Your original post which started this thread discussed the issue of fractional months , so I assume that is a component of this problem.

The only issue is whether we can arrive at a solution to the problem considering only months as a unit ; I think we cannot.

Taking dates from the first of a month to the first of another month should not be the deciding factor , since we have agreed that we need to consider fractional months. In such a case , the output in terms of months will have to look at a result in days , and then convert this result to months. How this is to be done is a matter of debate , and individual opinion / company policy. Period. No amount of discussion is going to change this.

Thus , if we are given two arbitrary dates , say 13-February-2016 and 27-March-2017 , how are we going to arrive at the 'real' number of months between them ?

Try to discuss this with your colleagues / manager , and only then can you arrive at an Excel formula.

Also point out to them the issue I have pointed out in an earlier comment of how the same period (in terms of days) will result in a different number of months depending of which months of the calendar are in between the two dates. This is what Rick Rothstein had pointed out in the link I posted.

Narayan
 
You must log in or register to reply here.
Back
Top