Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    dates difference (excel2003)

    hi all,
    can someone explain to me why the result is #VALUE! . i am trying to get the number of days between these two date.
    tia
    Attached Files Attached Files
    TIA
    dubdub

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

    Re: dates difference (excel2003)

    Your workbook has calculation set to Manual in the Edit tab of Tools | Options...
    If you select one of the cells with a formula, press F2 then Enter, the formula will be recalculated. This will cause C1 to be formatted as a date; you can apply Edit | Clear | Formats to display the result as a number.

    Note: cell A1 contains a text value, as you can see by making column A wider. The value will remain left-aligned, unlike the value in cell B1 which is a real date.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dates difference (excel2003)

    Thanks HansV,
    it works for the sample i posted earlier but when i applied it for more data it gives the same error massage. Any explanation why?
    Attached Files Attached Files
    TIA
    dubdub

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

    Re: dates difference (excel2003)

    It's because the values are text values, not real dates. You should use real dates. One way to do this is to edit each date cell (press F2 then Enter)

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dates difference (excel2003)

    is there any other easy way to do the conversion from text to date format?
    Regards,
    TIA
    dubdub

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

    Re: dates difference (excel2003)

    You can run this macro:

    Sub RepairDates()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    If IsDate(oCell) Then
    oCell = CDate(oCell)
    End If
    Next oCell
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dates difference (excel2003)

    HansV,
    thanks for your patient, I applied the code and it gives me different dates than the original one and unexpected results.
    Attached Files Attached Files
    TIA
    dubdub

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: dates difference (excel2003)

    Hi,
    Try this version:
    <pre>Sub RepairDates()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    oCell.Value = oCell.Formula
    Next oCell
    End Sub
    </pre>

    and see if that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: dates difference (excel2003)

    Check the number format - some of the cells have been formatted explicitly with US date format m/d/yy, others haven't.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dates difference (excel2003)

    hi rory,
    no change.
    Attached Files Attached Files
    TIA
    dubdub

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dates difference (excel2003)

    hi HansV,
    I standrized the format using cusotm category dd/mm/yyyy hh:mm and ran the code again but still getting unexpected results for the top two dates.
    regards
    TIA
    dubdub

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: dates difference (excel2003)

    What are the date format settings on your machine? If I open the file you posted and run the macro, it does exactly what you want, but I suspect that, like CDate, it is dependent on the date settings for your machine.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: dates difference (excel2003)

    Can you explain what you expect? We have no idea what is unexpected for you.

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

    Re: dates difference (excel2003)

    Why are the dates text values anyway? Are they imported or pasted from another application?

  15. #15
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dates difference (excel2003)

    My apology, you are totally right, I have changed the date format settings for my machine and it works fine, many thanks to you and HansV for your patient.
    TIA
    dubdub

Page 1 of 2 12 LastLast

Posting Permissions

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