static date on specific cell

EGP2018

New Member
Joined
Mar 14, 2018
Messages
13
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You are welcome.
Glad I was able to help!
:)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top