Results 1 to 8 of 8

Thread: static date on specific cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default static date on specific cell

    Hi,

    I can't seem to find an answer to this one anywhere.

    I have supervisors who add required delivery dates to a shares workbook.
    For example, if they want something delivered in a weeks time, they would add 1st June.

    I want to also log the date they put the date in, so that it doesn't update on future dates.

    Any ideas are welcome.

    Thanks

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: static date on specific cell

    A cell can either contain a hard-coded value (manually entered in or entered in by VBA), or a formula.
    Any formula that returns the current will always be changing, as it will return the current date, whatever it is on that day.
    So, you cannot have a formula return a current date value, and freeze it in time, at least not without VBA.

    How would you like to proceed?
    Is VBA an option?
    If so, just let us know the details (what cells are being updated, conditions when they should be updated, etc)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: static date on specific cell

    Quote Originally Posted by Joe4 View Post

    How would you like to proceed?
    Is VBA an option?
    If so, just let us know the details (what cells are being updated, conditions when they should be updated, etc)

    Thanks Joe4,

    I have columns that the supervisors enter the date - I guess the best option would be to have a column next to that (and hidden) to show today's date.
    Let's say for sake of argument that the future date is put in A1, then I'd like B1 to show today's day and remain as this date.

    Hope that makes sense?

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: static date on specific cell

    Here is some code that will automatically enter today's date in column B when something is entered in column A.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '   If a single cell in column A is updated, populate current date in column B of same row
        If Target.CountLarge > 1 Then Exit Sub
        If Target.Column = 1 Then Target.Offset(0, 1) = Date
    End Sub
    In order for this code to work, it must be placed in the correct module. Here is how you can do that:
    1. Right-click on the sheet tab name at the bottom of your screen
    2. Select "View Code"
    3. Paste the code above in the blank VB Editor window
    4. Save

    It will now work automatically. Note that Macros/VBA must be enabled to allow this to work.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: static date on specific cell

    Quote Originally Posted by Joe4 View Post
    Here is some code that will automatically enter today's date in column B when something is entered in column A.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '   If a single cell in column A is updated, populate current date in column B of same row
        If Target.CountLarge > 1 Then Exit Sub
        If Target.Column = 1 Then Target.Offset(0, 1) = Date
    End Sub
    In order for this code to work, it must be placed in the correct module. Here is how you can do that:
    1. Right-click on the sheet tab name at the bottom of your screen
    2. Select "View Code"
    3. Paste the code above in the blank VB Editor window
    4. Save

    It will now work automatically. Note that Macros/VBA must be enabled to allow this to work.

    Brilliant thanks - I'll test this afternoon and confirm tomorrow.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: static date on specific cell

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: static date on specific cell

    That worked perfectly - it solves a large problem so many thanks

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: static date on specific cell

    You are welcome.
    Glad I was able to help!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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