Results 1 to 8 of 8

Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

This is a discussion on Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates within the Excel Questions forums, part of the Question Forums category; Hi I want a Formula to calc the number of: YEARS , MONTHS , WEEKS and DAYS between two dates ...

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    115

    Default Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

    Hi I want a Formula to calc the number of:
    YEARS, MONTHS, WEEKS and DAYS between two dates

    I have tried the following:

    DATEDIF (Does NOT calculate the number of WEEKS)
    =DATEDIF(A1,A2,"y") & " Years, " & DATEDIF(A1,A2,"ym") & " Months, " & DATEDIF(A1,A2,"md") & " Days"

    I realise there are formulas', that JUST calculate the number of WEEKS, such as:
    =SUM(B151-B150)/7
    OR
    =INT((B151-B150)/7)


    HENCE TO REITERATE 'A' FORMULA to calculate the number of:
    YEARS, MONTHS, WEEKS and DAYS between two dates, would be great.

    OR even better 'A' FORMULA to calc the number of:
    YEARS, MONTHS, WEEKS, DAYS, HOURS, MINUTES and SECONDS between two dates and times (FORMAT CELLS, custom, dd/mm/yyyy hh:mm:ss) would be great.

    Cheers

    Stephan Rands

    07772000679

    mail@srands.co.uk

    www.srands.co.uk

  2. #2
    Board Regular
    Join Date
    Dec 2005
    Location
    Basingstoke (UK)
    Posts
    2,013

    Default Re: Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

    To get 'weeks' all you have to do is divide DAYS by 7 (use INT, ROUNDDOWN etc. to remove the decimal fraction). In a similar manner multiply by the appropriate manner to get HOURS, MINUTES etc.
    Also, given that a date is held as a number with time being the decimal fraction you could always subtract one from the other and then perform your calculations on the result.
    Never give way to anger - otherwise in one day you could burn up the wood that you collected in many bitter weeks.

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    115

    Default Re: Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

    I realise DAYS / 7 = WEEKS!

    I have considered what you suggested about many separate calculations, that is impractical.

    What I want is either 'A' FORMULA that calculates the:

    i) YEARS, MONTHS, WEEKS and DAYS between two dates.

    OR

    ii) YEARS, MONTHS, WEEKS, DAYS, HOURS, MINUTES and SECONDS between two dates and times (FORMAT CELLS, custom, dd/mm/yyyy hh:mm:ss).

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,855

    Default Re: Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

    David MrRitchie suggests this formula

    =DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"y")&" years, "&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"ym")&" months, "&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"md")&" days, "&TEXT(MOD(C2-B2,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds""")

    see his website for more

    That gives you everything but weeks. This revised version will give you weeks too (note: I removed the last MOD function in the above which isn't necessary)

    =DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"y")&" years, "&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"ym")&" months, "&INT(DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"md")/7)&" weeks, "&MOD(DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"md"),7)&" days, "&TEXT(C2-B2,"h"" hours, "" m"" minutes, and "" s"" seconds""")

  5. #5
    New Member
    Join Date
    Nov 2012
    Posts
    2

    Default Re: Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

    I used the modified formula that displays weeks but when I put in 8/1/12 to 9/30/12 it returns 1 month, 4 weeks, 1 days. I'm trying to figure out rental costs where we get reduced rates for months and weeks. The difference between these two dates should be: 2 months, 1 day. How do I get it to figure in this way?






    Quote Originally Posted by barry houdini View Post
    David MrRitchie suggests this formula

    =DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"y")&" years, "&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"ym")&" months, "&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"md")&" days, "&TEXT(MOD(C2-B2,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds""")

    see his website for more

    That gives you everything but weeks. This revised version will give you weeks too (note: I removed the last MOD function in the above which isn't necessary)

    =DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"y")&" years, "&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"ym")&" months, "&INT(DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"md")/7)&" weeks, "&MOD(DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"md"),7)&" days, "&TEXT(C2-B2,"h"" hours, "" m"" minutes, and "" s"" seconds""")

  6. #6
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,855

    Default Re: Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

    How do you get 2 months 1 day, even on the most generous side (from start of 8/1/12 to end of 9/30/12) that would only be 2 months, surely? Can you give some more examples of time periods and what result you would expect?

  7. #7
    New Member
    Join Date
    Nov 2012
    Posts
    2

    Default Re: Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

    I'm trying to create a budget for a rental car contracts.

    I figure full days including the beginning and ending day of the contract. Essentially rental the morning of 8/1 and return the evening of 9/30

    August (31 days) + September (30 days)=61 rental days.

    So for my purposes I need it to display the difference between these dates as: 2 months (of 30 days) and 1 day remaining. If there is a way to do this I would love to be able to modify my "month" length as some vendors bill on a 28 day month.

    I'm guessing my problem is to do with the way excel figures calendar months as opposed to actual days but I'm not sure.

    Eventually I'd love to be able to plug in the monthly, weekly, and daily rates and have it spit out my total cost.




    Quote Originally Posted by barry houdini View Post
    How do you get 2 months 1 day, even on the most generous side (from start of 8/1/12 to end of 9/30/12) that would only be 2 months, surely? Can you give some more examples of time periods and what result you would expect?

  8. #8
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,855

    Default Re: Formula to calc YEARS, MONTHS, WEEKS and DAYS between two dates

    OK, if you are counting 30 days as a month then try this version

    =INT((B2-A2+1)/30)&" months "&MOD(B2-A2+1,30)&" days"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com