Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subtracting dates (xp)

    Data, including two columns of dates, is captured and imported into Excel. The dates look like this - Feb. 8, 2006
    I have changed the formatting of the date columns to date formatting but the DAYS360 function gives me a value error when I try to use it to calculate the difference (i.e. number of days) between the two dates/
    If the columns are formatted as dates initially, will this never work?

    Thanks - Nancy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: subtracting dates (xp)

    You can subtract two dates very simply. Say the first date is in A2 and the last in B2. The formula =B2-A2 will calculate the difference in days! Only, Excel will format the result as a date, so select the cell with the formula and then select Edit | Clear | Formats. That's it!

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtracting dates (xp)

    With the period after Feb, Excel will store that date as a text value not as a date value. Select all of the cells with those dates in them and use Edit/Replace to replace the period with nothing. That should convert the text values to date values and then date functions and calculations should work properly.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtracting dates (xp)

    Thanks to you both.
    There was no period - so that wasn't an issue but I still get the #VALUE error.
    I've attached the sample.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtracting dates (xp)

    Same problem, different cause. There is no space between the comma and the year and Excel does not recognize that as a date. Use Edit/Replace to replace all of the commas with a comma and a space.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtracting dates (xp)

    You all are the BEST!
    Thanks!

Posting Permissions

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