+ Reply to Thread
Results 1 to 6 of 6

Dates

  1. #1
    Registered User
    Join Date
    02-07-2010
    Location
    Manchesteer
    MS-Off Ver
    Excel 2007
    Posts
    51

    Dates

    Hi,

    I've written this date formula to tell me how long someone has stayed from the date they moved in.

    Please Login or Register  to view this content.
    I would like it to ignore the years/months/days if they are 0 - e.g. 2 years 4 days rather than 2 years, 0 Months and 4 days - is there a way of doing this?

    Thanks,
    Mark

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Dates

    Not that trivial in a single calc...

    Please Login or Register  to view this content.
    The above would return Day / Month etc for a single unit rather than plural... however replacing the last comma with "and" is not quite so straightforward (ie it would be expensive in a single cell)

    Eg for 21-Mar-2009 what would be the desired output - 1 Year and 1 Month of 1 Year, 1 Month ?
    Last edited by DonkeyOte; 04-21-2010 at 04:18 AM. Reason: added CODE tags

  3. #3
    Registered User
    Join Date
    02-07-2010
    Location
    Manchesteer
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Dates

    That's great thanks - if you could get to to say '&' rather than the final comma that would be more aesthetically pleasing! But otherwise i'm happy.

    Mark

  4. #4
    Valued Forum Contributor
    Join Date
    10-06-2008
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    365

    Re: Dates

    I may be missing something, but isn't it possible to use the original approach like this?
    Please Login or Register  to view this content.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Dates

    Quote Originally Posted by mark-ainsworth View Post
    That's great thanks - if you could get to to say '&' rather than the final comma that would be more aesthetically pleasing! But otherwise i'm happy.

    Mark
    To be honest it would be a faff and purely because you don't know how many commas you will have for any given value without again analysing the results of each component.

    Personally, if viable, I'd be inclined to store the three numerical results in a separate cell (either as string or as number)... and subsequently create the final full-blown string from that value (thereby avoiding the repetitive DATEDIF calls).

    Quote Originally Posted by estige
    I may be missing something...
    Not as such - the suggestion I posted was to account for difference between 1 Year and 2 Years - ie one is plural the other is not.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Dates

    Having thought some more regards the final delimiter being ampersand - perhaps you could use:

    Please Login or Register  to view this content.

+ 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