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

 

[abdelfattah]

sorry fluff i have mistaken writing the function ok it shows and gives me the value but this is not accurate in col a= 1/7/2020 and b= 30/6/2021 if i calculate it not gives me 1 year it gives me 0 year the whole result 0 year 11 months 29 days when i tested alone days it is =364 it supposes 365 days so supposing 1 year
any advice ?

 

[Fluff]

Yes but using the vba datediff if you have 31/12/20 & 01/01/21 it will give you 1 day, 1 month 1 year.
Is that what you want?

 

[abdelfattah]

it doesn't make sense when you account of course i agree about 1 day but year and month are 0 the month depend s on 30 or 31 days based on the name of month and year based on 12 months

 

[Fluff]

Sorry but that makes absolutely no sense to me.
Are you happy with the results that the vba datdiff function returns?

 

[abdelfattah]

Are you happy with the results that the vba datdiff function returns?

yes

 

[Fluff]

In that case you will need to loop through the range.

 

[abdelfattah]

thanks fluff for your advice and time
best regards

 

[Fluff]

Glad to help.