Static date

[Hernan]

Hello,

I use =today()-1 in a cell to represent yesterday's date. However, how can
I make it static when saved?

TIA

Hernan

 

[Kevin B]

Instead of using the TODAY function, just type <CTRL> + ; to insert the
current system date as a static entry.

You can also insert the system time as a static entry with <Shift> + <CTRL>
+ ;

Hope this helps

 

[Gary''s Student]

enter:
CNTRL-;
rather than:
=TODAY()

 

[Stephen Eccleston]

Hi,

Which version of Excel will this work with?

in Excel 2003, <Ctrl>+ wants to insert cells.

Stephen

 

[Stephen Eccleston]

Hi,

lol - '<Ctrl> + ;' or '<Ctrl> and ;' pressed simultaneously.

Stephen

 

[Kevin B]

The <Ctrl> + is not a literal plus sign it means while holding down the
control key, press the semicolon key.

<Ctrl> + ;

 

[Gord Dibben]

None of the suggested CTRL + ; will give you yesterday's date.

Your formula will but as you note, wants to update every day.

You could use before_save event code to save the date as static but that would
wipe out the formula.

Maybe don't have a formula and have the event code insert yesterday's date when
the workbook is saved.

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = Format(Date - 1, "dd/mm/yyyy")
End Sub


Gord Dibben MS Excel MVP

 

[Hernan]

Hi Gord,

Thank you for noticing that I needed the date for the day before current.

I think your solution is going to work. I need to get rid of the protection.
I will let you know about this.

Thanks again.

Hernan

 

[Gord Dibben]

What type of protection?

If sheet protection, add an unprotect line at top and a protect line at end.

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("Sheet1")
.Unprotect Password:="justme"
.Range("A1").Value = Format(Date - 1, "dd/mm/yyyy")
.Protect Password:="justme"
End With
End Sub


Gord