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

1. ## 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 =)

2. ## 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. ## Re: Duration Between Two Dates In Month, Week And Days

Book1.xlsx

Ok, this is the file =)

4. ## 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

5. ## 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.!!!

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