Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping date from changing
I'm having a problem trying to get this formula to work
=IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))). The date keeps changing each day. Is formula works fine =IF(B3="","",IF(C3="",NOW(),C3)). Tools/options/calculations/Iterations. Can anyone please help me with the first formula. Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping date from changing
Richard,
As it happens, the date does change each day. Thus NOW() returns the current date. If you want something to look at those cells and plop in a fixed date, it'll take a macro. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Richard" wrote in message ... I'm having a problem trying to get this formula to work =IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))). The date keeps changing each day. Is formula works fine =IF(B3="","",IF(C3="",NOW(),C3)). Tools/options/calculations/Iterations. Can anyone please help me with the first formula. Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping date from changing
This is an example using cell A1. In A1 we have:
=IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))) The following worksheet event macro waits for A1 to become "visible": Private Sub Worksheet_Calculate() Set a1 = Range("A1") t = a1.Value nw = "NOW()" rp = "date(" & Year(Now()) & "," & Month(Now()) & "," & Day(Now()) & ")" If Len(t) = 0 Then Exit Sub f = a1.Formula If InStr(f, nw) = 0 Then Exit Sub Application.EnableEvents = False a1.Formula = Replace(f, nw, rp) Application.EnableEvents = True End Sub The macro sees that the formula contains NOW() and changes it into: =IF(C4=0,DATE(2008,4,26),IF(C40,"",IF(E4="",DATE( 2008,4,26),E4))) The macro is smart enough to use the correct values in DATE(). The macro is also smart enough to "fix" the formula only one time. 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 - gsnu200781 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping date from changing
Thanks for you're help Earl but there is a way to do this without writing a macro because this formula works =IF(B3="","",IF(C3="",NOW(),C3)). Tools/options/calculations/iteration. And the date will not change . I just can't figure out how to rewrite this formula do get the same results. =IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))). "Earl Kiosterud" wrote: Richard, As it happens, the date does change each day. Thus NOW() returns the current date. If you want something to look at those cells and plop in a fixed date, it'll take a macro. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Richard" wrote in message ... I'm having a problem trying to get this formula to work =IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))). The date keeps changing each day. Is formula works fine =IF(B3="","",IF(C3="",NOW(),C3)). Tools/options/calculations/Iterations. Can anyone please help me with the first formula. Thanks in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping date from changing
Richard
From John McGimpsey................... Using circular references and worksheet functions You can use a circular reference to enter the time when a change is made in another cell, then maintain that time. Choose Tools/Options/Calculation (Preferences/Calculation for Macs) and check the Iteration checkbox. Then, if your target cell is A1 and you want the date/time to appear in B1, enter this in B1: =IF(A1="","",IF(B1="",NOW(),B1)) Format B1 as you wish to display date, time, or both. If A1 is initially blank, B1 will return a null string (""). When a value is entered into A1, B1 will evaluate as "", therefore NOW() will be returned. After that (as long as A1 remains populated), B1 will evaluate to a date/time and therefore will return the value in B1 - i.e., the date/time. .................................................. . Gord Dibben MS Excel MVP On Sat, 26 Apr 2008 09:29:00 -0700, Richard wrote: Thanks for you're help Earl but there is a way to do this without writing a macro because this formula works =IF(B3="","",IF(C3="",NOW(),C3)). Tools/options/calculations/iteration. And the date will not change . I just can't figure out how to rewrite this formula do get the same results. =IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))). "Earl Kiosterud" wrote: Richard, As it happens, the date does change each day. Thus NOW() returns the current date. If you want something to look at those cells and plop in a fixed date, it'll take a macro. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Richard" wrote in message ... I'm having a problem trying to get this formula to work =IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))). The date keeps changing each day. Is formula works fine =IF(B3="","",IF(C3="",NOW(),C3)). Tools/options/calculations/Iterations. Can anyone please help me with the first formula. Thanks in advance! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping date from changing
On Apr 26, 11:55*am, Gord Dibben <gorddibbATshawDOTca wrote:
Richard From John McGimpsey................... Using circular references and worksheet functions You can use a circular reference to enter the time when a change is made in another cell, then maintain that time. Choose Tools/Options/Calculation (Preferences/Calculation for Macs) and check the Iteration checkbox. Then, if your target cell is A1 and you want the date/time to appear in B1, enter this in B1: =IF(A1="","",IF(B1="",NOW(),B1)) Format B1 as you wish to display date, time, or both. If A1 is initially blank, B1 will return a null string (""). When a value is entered into A1, B1 will evaluate as "", therefore NOW() will be returned. After that (as long as A1 remains populated), B1 will evaluate to a date/time and therefore will return the value in B1 - i.e., the date/time. .................................................. Gord Dibben *MS Excel MVP On Sat, 26 Apr 2008 09:29:00 -0700, Richard wrote: Thanks for you're help Earl but there is a way to do this without writing a macro because this formula works =IF(B3="","",IF(C3="",NOW(),C3)). Tools/options/calculations/iteration. And the date will not change . I just can't figure out how to rewrite this formula do get the same results. =IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))). "Earl Kiosterud" wrote: Richard, As it happens, the date does change each day. *Thus NOW() returns the current date. *If you want something to look at those cells and plop in a fixed date, it'll take a macro. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Richard" wrote in message ... I'm having a problem trying to get this formula to work =IF(C4=0,NOW(),IF(C40,"",IF(E4="",NOW(),E4))). The date keeps changing each day. Is formula works fine =IF(B3="","",IF(C3="",NOW(),C3)). Tools/options/calculations/Iterations. Can anyone please help me with the first formula. Thanks in advance!- Hide quoted text - - Show quoted text - Your date changes in both formulas because NOW() changes every day. You can lock it in by F-2 and F-9. Then that cell will show the "Locked-in" date rather NOW(). I don't know how to do that automatically as I have been trying to for YEARS. ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I drag a formula keeping cells the same but changing sheets | Excel Worksheet Functions | |||
Keeping the format of a date | Excel Worksheet Functions | |||
Keeping a date when today's date changes | New Users to Excel | |||
How do I fill down keeping one cell calculation from changing in . | Excel Discussion (Misc queries) | |||
Keeping a transfered value the same after changing the original va | Excel Worksheet Functions |