static date on specific cell

[EGP2018]

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

 

[Joe4]

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)

 

[EGP2018]

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?

 

[Joe4]

Here is some code that will automatically enter today's date in column B when something is entered in column A.

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.

 

[EGP2018]

Here is some code that will automatically enter today's date in column B when something is entered in column A.

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.

 

[Joe4]

You are welcome.

 

[EGP2018]

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

 

[Joe4]

You are welcome.
Glad I was able to help!