Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
(IF FUNTION )HOW TO SHORT THIS FORMULA?? | Excel Worksheet Functions | |||
How do I find age in years from anniversary date (Age funtion)? | Excel Worksheet Functions | |||
Excel date funtion | Excel Discussion (Misc queries) | |||
formula to differenciate whether a cell contains a funtion or numb | Excel Worksheet Functions | |||
Date Value Funtion in Excel 2003 returns an error, but not in Exce | Excel Worksheet Functions |