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

**jonathanpalmer** · 09-21-2010, 03:05 AM

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

Please can somebody help!

**helen82** · 09-21-2010, 03:38 AM

I wonder to know Which window version are you using?

**BlastRanger** · 09-21-2010, 04:42 AM

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)

**daddylonglegs** · 09-21-2010, 05:09 AM

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"

**jonathanpalmer** · 09-22-2010, 03:04 AM

Originally Posted by daddylonglegs

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!