Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2010
    Location
    Chicago, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert cells from General to Date Format

    I am trying to calculate the # of days between 2 date columns, however, the in the export that I received, excel is not recognizing it as date fields. Would you be able to help convert columns b & c in the attached to date fields? I have several other spreadsheets that I need to do the same with.

    Thank you so much in advance for any help you can provide!
    Dory

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger
    Join Date
    Feb 2010
    Location
    Chicago, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry....here is the attachment
    Attached Files Attached Files

  4. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 Posts
    Shotz,

    The reason why you are having problems is because the dates are not really dates but rather text. Even if you change the cell format to date, which I did, then try to subtract the 2 values, you are still subtracting text minus text (#value! error). I first ran a short bit of code to convert them to dates from text and now it works fine.

    Dates.jpg


    Code:
    Public Sub test()
    For I = 2 To 122
    Cells(I, 2).Value = CDate(Cells(I, 2).Value)   'CONVERT SUBMITTED VALUE TO DATE
    Cells(I, 3).Value = CDate(Cells(I, 3).Value)   'CONVERT OBTAINED VALUE TO DATE
    Next I
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-04-16 at 18:42.

  5. #4
    New Lounger
    Join Date
    Feb 2010
    Location
    Chicago, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much! This is exactly what I am looking for, however, can you send instructions as to how to run the code to convert the text to dates? I have some other spreadsheets for which I need to do the same.

    Thanks again - you are the best!!!

  6. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 Posts
    Dory,

    Thank you! I have modified the code so it will convert selected cells. If you are going to use the workbook I uploaded (Woody_Revised2) then the code is already in place. Run the code with the instructions below. If you are using your own workbook, then we will have to add the code. Once placed, follow the direction below to run it. To place the code:
    1. Press Alt-F11 to open the VB editor
    2. On the menu bar above click Insert>Module. A white screen will appear.
    3. Paste the new code below into the screen then "X" out of the editor.

    VBeditor.jpg


    Highlight the cells to convert then run the code by following these instructions.
    You did not specify which version of Excel but if:
    version 2007 or 2010
    -Click on Developer tab> Macros> highlight ConvertToDate> click Run

    Macro.jpg

    Version 2003
    -Click on Tools> Macro> Macros...> highlight ConvertToDate> click Run

    Macro2003.JPG
    Code:
    Public Sub ConvertToDate()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = CDate(cell.Value)
    Next cell
    End Sub
    NOTE: If you have version 2007 or above, you will have to save your file with the .xlsm extension because it now has a macro.

    Post back if you have any questions.
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2013-04-17 at 09:29.

  7. #6
    New Lounger
    Join Date
    Feb 2010
    Location
    Chicago, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh this is just wonderful - it works great with your instructions!!

    THANK YOU SO MUCH!!!

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
  •