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

Thanks:  0
Likes:  0

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

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

3. ## 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. ## 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. ## 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?

Originally Posted by barry houdini
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. ## 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. ## 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.

Originally Posted by barry houdini
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. ## 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"

## User Tag List

#### Posting Permissions

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