# days, months and years between todays date and a series of dates

1. ## days, months and years between todays date and a series of dates

I am trying to show the number of days, months and years between todays date and a series of dates which have either expired or will expire in the future. However, I need one formulae that will calculate positive and negative time eg:

Todays Date Contract date Time until expirey/past expirey
21//09/2010; 1/11/2010; 0 years 1 months 11 days
21/09/2010; 20/08/2010; 0 years -1 months - 1 days
21/09/2010; 22/09/2010; 0 years 1 month 1 days

2. ## Re: days, months and years between todays date and a series of dates

I wonder to know Which window version are you using?

3. ## Re: days, months and years between todays date and a series of dates

Hi,
Refer to the attachment for my Formula that works based on your criteria.
The only problem lies in the days, sometime the days will correct, sometimes will different about 1 day (because of day in each month is not always the same)

So the day should be -1 day, or +1 day (it Depend)

4. ## Re: days, months and years between todays date and a series of dates

Another option would be to use DATEDIF function, something like this

=TEXT(DATEDIF(MIN(A2,B2),MAX(A2,B2),"y"),IF(A2>B2,"-0;0;0","0"))&" Years "&TEXT(DATEDIF(MIN(A2,B2),MAX(A2,B2),"ym"),IF(A2>B2,"-0;0;0","0"))&" Months "&TEXT(DATEDIF(MIN(A2,B2),MAX(A2,B2),"md"),IF(A2>B2,"-0;0;0","0"))&" Days"

5. ## Re: days, months and years between todays date and a series of dates

Another option would be to use DATEDIF function, something like this

=TEXT(DATEDIF(MIN(A2,B2),MAX(A2,B2),"y"),IF(A2>B2,"-0;0;0","0"))&" Years "&TEXT(DATEDIF(MIN(A2,B2),MAX(A2,B2),"ym"),IF(A2>B2,"-0;0;0","0"))&" Months "&TEXT(DATEDIF(MIN(A2,B2),MAX(A2,B2),"md"),IF(A2>B2,"-0;0;0","0"))&" Days"
Thanks for the example. It aslo worked. Perhaps you coudl help me again? No pressure though.

I am trying to do some conditional formatting but I cant seem to get the following results as per my table below. Excel doesnt seem to recognise the transition from positive to the negative ie less than 1 month into negative days, months and years.

eg
1 Years 1 Months 0 Days
0 Years 1 Months 22 Days
-10 Years -6 Months -13 Days

conditions
greater than 3 months green
greater than 1 month orange
less than 1 month red

Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1