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

#### abdelfattah

##### Well-known Member
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
ABCDE
1dat1dat2daysmonthyears
201/01/201605/05/2017
302/01/201606/05/2017
403/02/201607/06/2017
504/02/201608/05/2017
605/02/201609/05/2017
706/02/201610/07/2017
807/02/201611/05/2017
908/02/201612/08/2017
1009/05/201613/05/2017
1110/02/201614/05/2017
1211/02/201615/09/2017
1312/04/201616/05/2017
1413/02/201617/10/2017
1514/02/201618/05/2017
1615/02/201619/05/2017
1716/02/201620/11/2017
1817/03/201621/05/2017
1918/02/201622/05/2017
2019/02/201623/12/2017
2120/02/201624/05/2017
2221/02/201625/05/2017
ورقة3

Last edited:

#### abdelfattah

##### Well-known Member
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Fluff

##### MrExcel MVP, Moderator
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

##### Well-known Member
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

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

#### abdelfattah

##### Well-known Member

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

#### Fluff

##### MrExcel MVP, Moderator
In that case you will need to loop through the range.

best regards

Replies
0
Views
91
Replies
4
Views
291
Replies
2
Views
255
Replies
6
Views
457
Replies
6
Views
486