Hello,

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

TIA

Hernan

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
--
Kevin Backmann


"Hernan" wrote:

Hello,

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

TIA

Hernan

enter:
CNTRL-;
rather than:
=TODAY()
--
Gary''s Student - gsnu200778


"Hernan" wrote:

Hello,

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

TIA

Hernan

Hi,

Which version of Excel will this work with?

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

Stephen

"Gary''s Student" wrote:

enter:
CNTRL-;
rather than:
=TODAY()
--
Gary''s Student - gsnu200778


"Hernan" wrote:

Hello,

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

TIA

Hernan

Hi,

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

Stephen

"Stephen Eccleston" wrote:

Hi,

Which version of Excel will this work with?

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

Stephen

"Gary''s Student" wrote:

enter:
CNTRL-;
rather than:
=TODAY()
--
Gary''s Student - gsnu200778


"Hernan" wrote:

Hello,

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

TIA

Hernan

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

<Ctrl + ;
--
Kevin Backmann


"Stephen Eccleston" wrote:

Hi,

Which version of Excel will this work with?

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

Stephen

"Gary''s Student" wrote:

enter:
CNTRL-;
rather than:
=TODAY()
--
Gary''s Student - gsnu200778


"Hernan" wrote:

Hello,

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

TIA

Hernan

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

On Fri, 11 Apr 2008 08:55:00 -0700, Hernan
wrote:

Hello,

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

TIA

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" wrote:

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

On Fri, 11 Apr 2008 08:55:00 -0700, Hernan
wrote:

Hello,

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

TIA

Hernan

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

On Sat, 12 Apr 2008 21:41:05 -0700, Hernan
wrote:

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" wrote:

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

On Fri, 11 Apr 2008 08:55:00 -0700, Hernan
wrote:

Hello,

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

TIA

Hernan