+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Duration Between Two Dates In Month, Week And Days

  1. #1
    Registered User
    Join Date
    02-15-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Duration Between Two Dates In Month, Week And Days

    Untitled.jpg

    Hye Pal,

    I'm looking any way to insert formula in column "Duration" in format as per instance above? Meaning that, if the duration is 1 month and above, the extra days is shown in days not in week. If the the duration is 1 week and above (not exceed than 1 month), the extra days is shown in days. Also, if the duration is 2 week for instance, it will only appear 2 weeks instead of "0 month 2 weeks 0 days". It is possible to be that way?? Any expertise out there?? Please help =)
    Thanks in advance!
    Last edited by aduka_irwana; 02-24-2012 at 12:00 PM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Duration Between Two Dates In Month, Week And Days

    Please post a sample sheet instead of a picture. No one is keen on having to type data needlessly.

  3. #3
    Registered User
    Join Date
    02-15-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Duration Between Two Dates In Month, Week And Days

    Book1.xlsx

    Ok, this is the file =)

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

    Re: Duration Between Two Dates In Month, Week And Days

    Try this formula

    =TRIM(IF(B2-A2>28,INT((B2-A2+1)/30)&" month"&IF(B2-A2>58,"s "," "),"")&IF(B2-A2>5,IF(B2-A2<29,INT((B2-A2+1)/7)&" week"&IF(B2-A2>12,"s "," "),""),"")&IF(B2-A2>28,IF(MOD(B2-A2+1,30),MOD(B2-A2+1,30)&" day"&IF(MOD(B2-A2+1,30)>1,"s",""),""),IF(MOD(B2-A2+1,7),IF(MOD(B2-A2+1,7)=1,"1 day",MOD(B2-A2+1,7)&" days"),"")))
    Edit: formula doesn't seem to copy and paste very well - see attachment with example, random dates in column B, press F9 to re-generate - formula in D2 copied down column
    Attached Files Attached Files
    Last edited by daddylonglegs; 02-24-2012 at 10:16 AM.
    Audere est facere

  5. #5
    Registered User
    Join Date
    02-15-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Duration Between Two Dates In Month, Week And Days

    Wow! You're really brilliant daddylonglegs...How can you generate this formula huh? It's amazing.!!!

+ 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