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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,091
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,091
Office Version
365
Platform
Windows
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.
 

EGP2018

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,091
Office Version
365
Platform
Windows
You are welcome.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,091
Office Version
365
Platform
Windows
You are welcome.
Glad I was able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,096,193
Messages
5,448,887
Members
405,537
Latest member
muh6323

This Week's Hot Topics

Top