+ Reply to Thread
Results 1 to 6 of 6

How to Calculate Date Differnce in days, weeks or months

  1. #1
    Registered User
    Join Date
    09-14-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    83

    How to Calculate Date Differnce in days, weeks or months

    Can someone help me create a formula to help calculate the date difference between Weeks, Days, and Months(excluding time)

    I have included a sample xlsx file with sample date. I tried with the following but it failed.
    1/23/2024, 12:00 AM 1/31/2024, 12:00 AM
    =(DATEDIF(A1,B1,"d")/7)
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,690

    Re: How to Calculate Date Differnce in days, weeks or months

    The problem is that your dates are not numbers, they are text. The date dif formula works with numbers (formatted as dates).
    try to use find and replace, find what >> , (comma) and replace with >> leave blank.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    09-14-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    83

    Re: How to Calculate Date Differnce in days, weeks or months

    Hi Sam

    Thanks for getting in touch.

    I tried removing the commas but I still get an error

  4. #4
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: How to Calculate Date Differnce in days, weeks or months

    There are 2 problems, the dates are not only text, but the dates are also formatted: m/d/yyyy, but in England if date is in text, Excel expects format dd/mm/yyyy (or d/m/yyyy).
    So first you should switch the date and the month in the string before you can convert the string to a date.

    And the formula for this is in Excel 2010 more difficult, because you have to figure out exactly where the day and year start. After all, the month can take up 1 or 2 positions and perhaps the day of the month too. That is more difficult in Excel 2010 than in Office 365.

    Are you still using Excel 2010?

  5. #5
    Registered User
    Join Date
    09-14-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    83

    Re: How to Calculate Date Differnce in days, weeks or months

    Hi HansDouwe,

    I am using Office 365


    I changed the dates to:
    15/01/2024
    31/03/2024

    And then used the following formula:

    =INT(DATEDIF(IF(B1<B2,B1,B2),IF(B1>B2,B1,B2),"d")/7)&" week(s) "&MOD(DATEDIF(IF(B1<B2,B1,B2),IF(B1>B2,B1,B2),"d"),7)&""&" day(s) "&""

  6. #6
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: How to Calculate Date Differnce in days, weeks or months

    OK, then you've already solved a lot yourself.

    I'll tried your formula and it returns 10 week(s) and 6 day(s)

    Are there any questions left?

    Would you also like to update your MS-Off version in your profile?
    Otherwise you will not be presented with the formulas that best suit your current version.

    The differences between the different MS-Off versions are very large.
    Last edited by HansDouwe; 01-13-2024 at 09:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate weeks if date is less than 7 days from end of month
    By marcquey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2021, 07:17 AM
  2. Count days, weeks and months between date range
    By ExcelRules2020 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-06-2020, 09:43 PM
  3. Calculate Number of weeks/months falls under each year between date range
    By dhitpit in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2017, 10:06 AM
  4. Replies: 10
    Last Post: 02-22-2017, 04:51 AM
  5. [SOLVED] Calculate date based on number of weeks or months after start date
    By wotsup in forum Excel General
    Replies: 3
    Last Post: 08-17-2012, 06:00 AM
  6. Replies: 1
    Last Post: 06-29-2006, 09:30 AM
  7. [SOLVED] How do I sort by date (not days, weeks, months) in Excel 2000?
    By Tony in forum Excel General
    Replies: 1
    Last Post: 01-21-2005, 12:06 PM

Tags for this Thread

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