+ Reply to Thread
Results 1 to 4 of 4

Automatic Static Date

  1. #1
    Registered User
    Join Date
    04-29-2012
    Location
    New England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Automatic Static Date

    Hello,

    I have a spreadsheet at work that automatically enters a "Y" in column "I" once data is entered in previous columns. The "Y" in column "I" triggers the date to be entered in column "K". I need this date to be static since it refers to calibration data that is done on a monthly basis and a new sheet is created for each month.

    The formula I currently have in the "K" column is =IF(I30="Y",TODAY(),"") (this example happened to be column I row 30)

    What vba code would be best to make this date static?

    I am very new to Excel formulas and Vba code so please go easy on me.

    Thank you for the help,
    Rob

  2. #2
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    590

    Re: Automatic Static Date

    If I've read this right, because you are using a formula, it is incrementing on subsequent days when the workbook is opened/manipulated?

    The code below will convert your formula based date to a static string date for any selection of date cells (meaning you will need to highlight the range of cells that you would like converted prior to running the code.

    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE)
    3. From the insert menu, select "Module".
    4. Paste the code from below into the empty white space.
    5. Close the VBE.
    6. Select the range of dates you would like converted.
    7. From the developer tab, choose "Macros", select "StringIt", and click "Run".

    copy to clipboard
    Please Login or Register  to view this content.
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

  3. #3
    Registered User
    Join Date
    04-29-2012
    Location
    New England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Automatic Static Date

    Thank you AlvaroSiza, I will give that a try tonight. .....and yes, each time the workbook is opened the dates are changed to the current date and that is what I am trying to correct.

  4. #4
    Registered User
    Join Date
    04-29-2012
    Location
    New England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Automatic Static Date

    I hope this is an easy fix.... I decided to take a different approach than offered above because I want to try and keep the date entry automatic. So I found the code below and tried that. It works great except for one thing. If a cell in column "I" is populated automatically via a function I'm using, the date does not appear in column "K". However, If I manually enter anything in any of the cells in column "I" the date does appear, and that is what I want. Why isn't Excel acknowledging that the cell has an entry after a function populates the cell?



    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("I8:I59, I64:I67" & Cells(Rows.Count, 9).End(xlUp).Row)) Is Nothing Then
    If IsEmpty(Target) Then
    Target.Offset(0, 2) = ""
    Else:
    Target.Offset(0, 2) = Date
    End If
    End If
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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