Need different between date

[sksanjeev786]

Hi Team,

I need difference between 2 dates like D2 - D3 =2 month and XXX days.

book1
	A	B	C	D	E	F	G
1	S.No	Date of Joining					Different
2	1	5/1/2006		14 years 11 months 12 Days
3	2	6/26/2006		14 years 9 months 18 Days
4	3	3/19/2007		14 years 0 months 25 Days
5	4	5/11/2007		13 years 11 months 2 Days
6
7
Sheet2

Cell Formulas
Range	Formula
A3:A5	A3	=A2+1
D2:D5	D2	=DATEDIF(B2,TODAY(),"y")&" years "&DATEDIF(B2,TODAY(),"ym")&" months "&DATEDIF(B2,TODAY(),"md")&" Days "

 

[Peter_SSs]

Is this what you mean? If not, more detail and the exact expected result(s) please.

21 04 13.xlsm
	B	C	D	E	F	G
1	Date of Joining					Different
2	1/05/2006		14 years 11 months 12 Days			1 months 25 Days
3	26/06/2006		14 years 9 months 18 Days			8 months 21 Days
4	19/03/2007		14 years 0 months 25 Days			1 months 22 Days
5	11/05/2007		13 years 11 months 2 Days
Sheet2 (2)

Cell Formulas
Range	Formula
G2:G4	G2	=DATEDIF(B2,B3,"ym")&" months "&DATEDIF(B2,B3,"md")&" Days "
D2:D5	D2	=DATEDIF(B2,TODAY(),"y")&" years "&DATEDIF(B2,TODAY(),"ym")&" months "&DATEDIF(B2,TODAY(),"md")&" Days "

 

[sksanjeev786]

Is this what you mean? If not, more detail and the exact expected result(s) please.

21 04 13.xlsm
	B	C	D	E	F	G
1	Date of Joining					Different
2	1/05/2006		14 years 11 months 12 Days			1 months 25 Days
3	26/06/2006		14 years 9 months 18 Days			8 months 21 Days
4	19/03/2007		14 years 0 months 25 Days			1 months 22 Days
5	11/05/2007		13 years 11 months 2 Days
Sheet2 (2)

Cell Formulas
Range	Formula
G2:G4	G2	=DATEDIF(B2,B3,"ym")&" months "&DATEDIF(B2,B3,"md")&" Days "
D2:D5	D2	=DATEDIF(B2,TODAY(),"y")&" years "&DATEDIF(B2,TODAY(),"ym")&" months "&DATEDIF(B2,TODAY(),"md")&" Days "

Hey Peter,

Thanks for your help on this but i need to calculation on Column D (D2-D3) as i have huge data similar to Column D.

Thanks.

 

[Peter_SSs]

but i need to calculation on Column D (D2-D3) as i have huge data similar to Column D.

Why specifically from column D as those results come from column B anyway?

Forgetting the method for a moment, are the results in my sheet the results you want?

 

[sksanjeev786]

Why specifically from column D as those results come from column B anyway?

Forgetting the method for a moment, are the results in my sheet the results you want?

Hello Peter,

the result has come perfectly. but in my data base i have a data in Column D format not in Column B formate.

so just wanted to check it is possible to do the calculation via D coliumn

Thanks in advance

Thanks.
Sanjeev

 

[Peter_SSs]

wanted to check it is possible to do the calculation via D coliumn

To consider that, could you post the column D data again but include your expected results, worked out manually, and entered into column E and explain how you went about it manually?

 

[sksanjeev786]

To consider that, could you post the column D data again but include your expected results, worked out manually, and entered into column E and explain how you went about it manually?

Sure Peter

below is data

Book1
	A	B	C
1	Year spend in Org.		Different
2	14 years 11 months 12 Days		#VALUE!
3	14 years 9 months 18 Days		#VALUE!
4	14 years 0 months 25 Days		#VALUE!
5	13 years 11 months 2 Days
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=DATEDIF(A2,A3,"ym")&" months "&DATEDIF(A2,A3,"md")&" Days "

Thanks in advance.
Regards,
Sanjeev

 

[Peter_SSs]

Sure Peter

below is data

Sorry, but I asked for the actual results you want in column C, not a formula that does not do what you want.

 

[sksanjeev786]

Sorry, but I asked for the actual results you want in column C, not a formula that does not do what you want.

Here is the data in Column C

Book1
	A	B	C
1	Year spend in Org.		Different
2	14 years 11 months 12 Days		1 month 25 days
3	14 years 9 months 18 Days		8 month 21 days
4	14 years 0 months 25 Days		1 month 21 days
5	13 years 11 months 2 Days
Sheet1

Thanks in advance
Sanjeev

 

[Peter_SSs]

I don't see how you came up with some of those results - & remember that I did ask you ..

and explain how you went about it manually

Presumably you have made an assumption about the number of days in a month?

My calculation (using 30 days for a month) of 8 months 21 days after 14 years 0 months 25 days would be 14 years 9 months 16 days yet A3 has 18 days. The only way to end up with 18 days that I can see is to assume 28 days in a month which would normally be incorrect as I see it.

My calculation of 1 month 21 days after 13 years 11 months 2 days would be 14 years 0 months 23 days. Again, the way to end up with 25 days as shown in A4 would be to use 28 days for a month.

However, if 28 days was used for a month the first result would be 1 month 22 days not 1 month 25 days that you have shown.