+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    09-21-2010
    Location
    Perth
    MS-Off Ver
    Excel 2003
    Posts
    2

    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

    Please can somebody help!

  2. #2
    Registered User
    Join Date
    09-20-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    1

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

    I wonder to know Which window version are you using?
    Last edited by helen82; 09-23-2010 at 02:41 AM.

  3. #3
    Forum Contributor BlastRanger's Avatar
    Join Date
    09-02-2010
    Location
    Indonesia
    MS-Off Ver
    2003, 2007, 2010
    Posts
    238

    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)
    Attached Files Attached Files

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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"
    Audere est facere

  5. #5
    Registered User
    Join Date
    09-21-2010
    Location
    Perth
    MS-Off Ver
    Excel 2003
    Posts
    2

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

    Quote Originally Posted by daddylonglegs View Post
    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!
    Last edited by jonathanpalmer; 09-22-2010 at 03:08 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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