| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time update
Hi there, I have a formula that updates the time wherever data is entered in the B15 cell. I copied this formula down the column so that each update (B16..B17) will have its on time update, the problem is that the latest time update updates all the previous times to the current time. Can someone help me rectify this please? I want times updated previously to be locked and not updated anymore. I have tried locking nearly everything in the formula but got thrown errors. Here is the formula, =IF(B15<"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND( NOW())),"") Thanks. -- gusdafa |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
First, your formula can be simplified to:
=IF(B15<"",NOW(),"") But the bad news is that this formula will reevalate each time excel recalcs. You could use enter the date and time by using a couple of shortcut keys: ctrl-; (spacebar) ctrl-: Or you could use an event macro that populates the cell when B15 (and more) change. See JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html gusdafa wrote: Hi there, I have a formula that updates the time wherever data is entered in the B15 cell. I copied this formula down the column so that each update (B16..B17) will have its on time update, the problem is that the latest time update updates all the previous times to the current time. Can someone help me rectify this please? I want times updated previously to be locked and not updated anymore. I have tried locking nearly everything in the formula but got thrown errors. Here is the formula, =IF(B15<"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND( NOW())),"") Thanks. -- gusdafa -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
The formula does not simplify to =IF(B15<"",NOW(),"") but to
=IF(B15<"",MOD(NOW(),1),""), but Dave is right that it would recalculate each time. -- David Biddulph "Dave Peterson" wrote in message ... First, your formula can be simplified to: =IF(B15<"",NOW(),"") But the bad news is that this formula will reevalate each time excel recalcs. You could use enter the date and time by using a couple of shortcut keys: ctrl-; (spacebar) ctrl-: Or you could use an event macro that populates the cell when B15 (and more) change. See JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html gusdafa wrote: Hi there, I have a formula that updates the time wherever data is entered in the B15 cell. I copied this formula down the column so that each update (B16..B17) will have its on time update, the problem is that the latest time update updates all the previous times to the current time. Can someone help me rectify this please? I want times updated previously to be locked and not updated anymore. I have tried locking nearly everything in the formula but got thrown errors. Here is the formula, =IF(B15<"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND( NOW())),"") Thanks. -- gusdafa -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
First, your formula can be simplified to:
=IF(B15<"",NOW(),"") The formula does not simplify to =IF(B15<"",NOW(),"") but to =IF(B15<"",MOD(NOW(),1),"") Or even... =IF(B15<"",NOW()-TODAY(),"") Rick "Dave Peterson" wrote in message ... First, your formula can be simplified to: =IF(B15<"",NOW(),"") But the bad news is that this formula will reevalate each time excel recalcs. You could use enter the date and time by using a couple of shortcut keys: ctrl-; (spacebar) ctrl-: Or you could use an event macro that populates the cell when B15 (and more) change. See JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html gusdafa wrote: Hi there, I have a formula that updates the time wherever data is entered in the B15 cell. I copied this formula down the column so that each update (B16..B17) will have its on time update, the problem is that the latest time update updates all the previous times to the current time. Can someone help me rectify this please? I want times updated previously to be locked and not updated anymore. I have tried locking nearly everything in the formula but got thrown errors. Here is the formula, =IF(B15<"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND( NOW())),"") Thanks. -- gusdafa -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
And thanks for alternative, too!
"Rick Rothstein (MVP - VB)" wrote: First, your formula can be simplified to: =IF(B15<"",NOW(),"") The formula does not simplify to =IF(B15<"",NOW(),"") but to =IF(B15<"",MOD(NOW(),1),"") Or even... =IF(B15<"",NOW()-TODAY(),"") Rick "Dave Peterson" wrote in message ... First, your formula can be simplified to: =IF(B15<"",NOW(),"") But the bad news is that this formula will reevalate each time excel recalcs. You could use enter the date and time by using a couple of shortcut keys: ctrl-; (spacebar) ctrl-: Or you could use an event macro that populates the cell when B15 (and more) change. See JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html gusdafa wrote: Hi there, I have a formula that updates the time wherever data is entered in the B15 cell. I copied this formula down the column so that each update (B16..B17) will have its on time update, the problem is that the latest time update updates all the previous times to the current time. Can someone help me rectify this please? I want times updated previously to be locked and not updated anymore. I have tried locking nearly everything in the formula but got thrown errors. Here is the formula, =IF(B15<"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND( NOW())),"") Thanks. -- gusdafa -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
You're right.
Thanks for the correction. David Biddulph wrote: The formula does not simplify to =IF(B15<"",NOW(),"") but to =IF(B15<"",MOD(NOW(),1),""), but Dave is right that it would recalculate each time. -- David Biddulph "Dave Peterson" wrote in message ... First, your formula can be simplified to: =IF(B15<"",NOW(),"") But the bad news is that this formula will reevalate each time excel recalcs. You could use enter the date and time by using a couple of shortcut keys: ctrl-; (spacebar) ctrl-: Or you could use an event macro that populates the cell when B15 (and more) change. See JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html gusdafa wrote: Hi there, I have a formula that updates the time wherever data is entered in the B15 cell. I copied this formula down the column so that each update (B16..B17) will have its on time update, the problem is that the latest time update updates all the previous times to the current time. Can someone help me rectify this please? I want times updated previously to be locked and not updated anymore. I have tried locking nearly everything in the formula but got thrown errors. Here is the formula, =IF(B15<"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND( NOW())),"") Thanks. -- gusdafa -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
Try JE McGimpsey's coverage of "Time and date stamps" at his page:
http://www.mcgimpsey.com/excel/timestamp.html Scroll down to the part: Using a worksheet event macro (You can't use formulas as the "date/time stamps" as these will recalc) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "gusdafa" wrote in message ... Hi there, I have a formula that updates the time wherever data is entered in the B15 cell. I copied this formula down the column so that each update (B16..B17) will have its on time update, the problem is that the latest time update updates all the previous times to the current time. Can someone help me rectify this please? I want times updated previously to be locked and not updated anymore. I have tried locking nearly everything in the formula but got thrown errors. Here is the formula, =IF(B15<"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND( NOW())),"") Thanks. -- gusdafa |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
Scroll down to the part: Using a worksheet event macro
You can't use formulas as the "date/time stamps" as these will recalc You scrolled right past "Using circular references and worksheet functions". -- Biff Microsoft Excel MVP "Max" wrote in message ... Try JE McGimpsey's coverage of "Time and date stamps" at his page: http://www.mcgimpsey.com/excel/timestamp.html Scroll down to the part: Using a worksheet event macro (You can't use formulas as the "date/time stamps" as these will recalc) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "gusdafa" wrote in message ... Hi there, I have a formula that updates the time wherever data is entered in the B15 cell. I copied this formula down the column so that each update (B16..B17) will have its on time update, the problem is that the latest time update updates all the previous times to the current time. Can someone help me rectify this please? I want times updated previously to be locked and not updated anymore. I have tried locking nearly everything in the formula but got thrown errors. Here is the formula, =IF(B15<"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND( NOW())),"") Thanks. -- gusdafa |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
Intent was to inform OP to try this part in JE's page:
Scroll down to the part: Using a worksheet event macro -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote in message ... Scroll down to the part: Using a worksheet event macro You can't use formulas as the "date/time stamps" as these will recalc You scrolled right past "Using circular references and worksheet functions". |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time update
Max;2642773 Wrote: Try JE McGimpsey's coverage of "Time and date stamps" at his page: http://www.mcgimpsey.com/excel/timestamp.html [/i][/color] Thanks a lot for the help. Problem resolved. :D -- gusdafa |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| update time | Excel Discussion (Misc queries) | |||
| excel access will not update time | Excel Worksheet Functions | |||
| How to get time to update automatically and continuos | Excel Worksheet Functions | |||
| Time stamp that does NOT update | Excel Worksheet Functions | |||
| How to continuously update time in Excel? | Excel Worksheet Functions | |||
Hybrid Mode
