Formula to help with Date funtion
 

Hi I'm currently using the following formula :

=IF(E7="","",E7-TODAY())

This works fine and works out for me the number of days between todays date
and another variable date. What I would like is that when cell L7 is
populated the amove formular stops and will forever show the last result.

To explain a little better, If my answer from the above formular is 21 and
today i imput data either text or number into L7, I want the 21 to stay
fixed, so when i open the sheet tomorrow, it will still read 21 and not
increase to 22

Thanks in advance

Formula to help with Date funtion
 

You can't use TODAy & it not change, TODAY() is a volatile function and
will always change to show today's date.


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94585

Formula to help with Date funtion
 

In what cell is the formula located?
--
Gary''s Student - gsnu200852


"btyler" wrote:

Hi I'm currently using the following formula :

=IF(E7="","",E7-TODAY())

This works fine and works out for me the number of days between todays date
and another variable date. What I would like is that when cell L7 is
populated the amove formular stops and will forever show the last result.

To explain a little better, If my answer from the above formular is 21 and
today i imput data either text or number into L7, I want the 21 to stay
fixed, so when i open the sheet tomorrow, it will still read 21 and not
increase to 22

Thanks in advance

Formula to help with Date funtion
 

Hi, It's I7

"Gary''s Student" wrote:

In what cell is the formula located?
--
Gary''s Student - gsnu200852


"btyler" wrote:

Hi I'm currently using the following formula :

=IF(E7="","",E7-TODAY())

This works fine and works out for me the number of days between todays date
and another variable date. What I would like is that when cell L7 is
populated the amove formular stops and will forever show the last result.

To explain a little better, If my answer from the above formular is 21 and
today i imput data either text or number into L7, I want the 21 to stay
fixed, so when i open the sheet tomorrow, it will still read 21 and not
increase to 22

Thanks in advance

Formula to help with Date funtion
 

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set L7 = Range("L7")
If Intersect(t, L7) Is Nothing Then Exit Sub
If L7.Value = "" Then Exit Sub
Set ff = Range("I7")
Application.EnableEvents = False
ff.Value = ff.Value
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

--
Gary''s Student - gsnu200852


"btyler" wrote:

Hi, It's I7

"Gary''s Student" wrote:

In what cell is the formula located?
--
Gary''s Student - gsnu200852


"btyler" wrote:

Hi I'm currently using the following formula :

=IF(E7="","",E7-TODAY())

This works fine and works out for me the number of days between todays date
and another variable date. What I would like is that when cell L7 is
populated the amove formular stops and will forever show the last result.

To explain a little better, If my answer from the above formular is 21 and
today i imput data either text or number into L7, I want the 21 to stay
fixed, so when i open the sheet tomorrow, it will still read 21 and not
increase to 22

Thanks in advance

Formula to help with Date funtion
 

Many Thanks for that

"Gary''s Student" wrote:

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set L7 = Range("L7")
If Intersect(t, L7) Is Nothing Then Exit Sub
If L7.Value = "" Then Exit Sub
Set ff = Range("I7")
Application.EnableEvents = False
ff.Value = ff.Value
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

--
Gary''s Student - gsnu200852


"btyler" wrote:

Hi, It's I7

"Gary''s Student" wrote:

In what cell is the formula located?
--
Gary''s Student - gsnu200852


"btyler" wrote:

Hi I'm currently using the following formula :

=IF(E7="","",E7-TODAY())

This works fine and works out for me the number of days between todays date
and another variable date. What I would like is that when cell L7 is
populated the amove formular stops and will forever show the last result.

To explain a little better, If my answer from the above formular is 21 and
today i imput data either text or number into L7, I want the 21 to stay
fixed, so when i open the sheet tomorrow, it will still read 21 and not
increase to 22

Thanks in advance