Results 1 to 6 of 6

Thread: Difference between dates in different date formats

  1. #1

    Difference between dates in different date formats



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    I know that calculating the difference between two dates is doable in excel using the subtraction or "DATEDIF" function if the date format is month/day/year.
    However, is it possible to calculate the difference between dates if the format is day/month/year? I tried it, but the result is (#Value!) even if I changed the format of the date cells to be in the format of day/month/year.
    Any Solution for this?
    I am using Excel 2013

  2. #2
    Sounds like the info in the cell is probably not actually a date. Look into various Date functions to convert to a proper date. A date is actually a number ie 3/23/2016 is really 42452. If you format the cell with the date to General you will see this, it does not, then it is not an actual date.

    If you post an example file would be easier to show how to sort this out. There are a variety of approaches possible depending on the actual data.

    Cheers

  3. #3
    Thanks Jafa.
    You can see the file attached. The format is custom:a date in the format of day/month/year
    Attached Files Attached Files

  4. #4
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,662
    Articles
    0
    Excel Version
    2010 on Xubuntu
    In your example, the date in B3 is a text string. You cannot see it easily because you right aligned the cell.
    If you remove this alignment, the string will align left in the cell indicating it is text.

    So, B3-B2 will not work because you cannot subtract number and text.
    There are various ways to solve the problem.
    One of them is to select the "text date" cell - Data ribbon - Click Text to columns, and immediately click Finish.

    Remember that to excel a date is an integer representing number of days elapsed since 01/01/1900 ( if not using the 1904 system). The formatting is how XL shows you a date but does NOT affect the underlying value

  5. #5
    Agree with what Pecoflyer says re formatting and dates really being numbers.

    That said, if you copy the formula in A5 to B5, it seems that Excel is able to understand the text as a date and calculate the difference. I was a bit surprised at this.

    Have played with the file and shown how you might convert the text to a date using formulas, in case you have a large data set to work with. Decided to separate the components to make it easier to follow, but you could combine into one large formula, would be difficult to read though. There are probably user defined functions (VBA code) on the web that do this too.
    Attached Files Attached Files

  6. #6
    Thanks Pecoflyer,
    I tried the way you mentioned to change the text date cell, however, the result is still the same: "#Value!".
    I changed the cell type into "Date", of short date type, but it did not work also, unfortunately.

Tags for this Thread

Posting Permissions

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