calculate dynamic between dates(days,months,years ) alternative datediff

[abdelfattah]

hello
if is possible to calculate days, months & years between two dates actually i use function datediff but this is not dynamic if do that by macro because i have at least 1000 rows
i would the result in col c after subtract a-b

SH.xlsm
	A	B	C	D	E
1	dat1	dat2	days	month	years
2	01/01/2016	05/05/2017
3	02/01/2016	06/05/2017
4	03/02/2016	07/06/2017
5	04/02/2016	08/05/2017
6	05/02/2016	09/05/2017
7	06/02/2016	10/07/2017
8	07/02/2016	11/05/2017
9	08/02/2016	12/08/2017
10	09/05/2016	13/05/2017
11	10/02/2016	14/05/2017
12	11/02/2016	15/09/2017
13	12/04/2016	16/05/2017
14	13/02/2016	17/10/2017
15	14/02/2016	18/05/2017
16	15/02/2016	19/05/2017
17	16/02/2016	20/11/2017
18	17/03/2016	21/05/2017
19	18/02/2016	22/05/2017
20	19/02/2016	23/12/2017
21	20/02/2016	24/05/2017
22	21/02/2016	25/05/2017
ورقة3

 

[Fluff]

What do you mean that datedif is not dynamic?

 

[abdelfattah]

hi, fluff
as far as i know it applies for specific cells not like in my case they contain many cells in col a,b so i no know if another function do that what i'm looking for

 

[Fluff]

You said you wanted to calculate it between two dates which is exactly what datedif does.
What exactly are you trying to do?

 

[abdelfattah]

as in picture it supposes subtract between col a, b and result days in col c, months col d years in col e so far this is what i got but it doesn't success

Sub ff()
Dim str, en As Date
Dim diff, diff1, dif2 As Integer
Dim lstrow As Integer
lstrow = Cells(Rows.Count, 5).End(xlUp)
str = Range("a" & lstrow).Value
en = Range("b" & lstrow).Value
diff = DateDiff("d", str, en)
diff1 = DateDiff("m", str, en)
diff2 = DateDiff("yyyy", str, en)
Range("c" & lstrow).Value = diff
Range("d" & lstrow).Value = diff1
Range("e" & lstrow).Value = diff2
End Sub

 

[Fluff]

If you want to do that for the entire range, rather than just the last row, then you will have to loop through the range.

 

[abdelfattah]

is it important do that by loop? if is possible i prefer do that without loop to avoid slowly the code

 

[Fluff]

Why not just use a formula

+Fluff v2.xlsm
	A	B	C	D	E
1	dat1	dat2	days	month	years
2	01/01/2016	05/05/2017	490	16	1
3	02/01/2016	06/05/2017	490	16	1
4	03/02/2016	07/06/2017	490	16	1
5	04/02/2016	08/05/2017	459	15	1
6	05/02/2016	09/05/2017	459	15	1
7	06/02/2016	10/07/2017	520	17	1
8	07/02/2016	11/05/2017	459	15	1
9	08/02/2016	12/08/2017	551	18	1
10	09/05/2016	13/05/2017	369	12	1
11	10/02/2016	14/05/2017	459	15	1
12	11/02/2016	15/09/2017	582	19	1
13	12/04/2016	16/05/2017	399	13	1
14	13/02/2016	17/10/2017	612	20	1
15	14/02/2016	18/05/2017	459	15	1
16	15/02/2016	19/05/2017	459	15	1
17	16/02/2016	20/11/2017	643	21	1
18	17/03/2016	21/05/2017	430	14	1
19	18/02/2016	22/05/2017	459	15	1
20	19/02/2016	23/12/2017	673	22	1
21	20/02/2016	24/05/2017	459	15	1
22	21/02/2016	25/05/2017	459	15	1
Sheet3

Cell Formulas
Range	Formula
C2:C22	C2	=DATEDIF(A2,B2,"d")
D2:D22	D2	=DATEDIF(A2,B2,"m")
E2:E22	E2	=DATEDIF(A2,B2,"y")

 

[abdelfattah]

the problem it doesn't show this function i no know if the reason is version of office

 

[Fluff]

It's an old function that has been deprecated, but still works.
You can see how to use it here

DATEDIF function - Microsoft Support

Calculates the number of days, months, or years between two dates.

support.microsoft.com