| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date function
Hi all,
I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! -- BGC |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Count = 1 Then _ Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "BC" wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! -- BGC |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
Jacob,
I tried this, closed, then reopened. File became read-only. Once I opened, the date changed again. Here's the formula I'm using. =if(m198=0,now()) -- BGC "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Count = 1 Then _ Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "BC" wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! -- BGC |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
To record the timestamp you cannot use a formula..You will have to use a VBA
solution as mentioned in my earlier post. If this post helps click Yes --------------- Jacob Skaria "BC" wrote: Jacob, I tried this, closed, then reopened. File became read-only. Once I opened, the date changed again. Here's the formula I'm using. =if(m198=0,now()) -- BGC "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Count = 1 Then _ Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "BC" wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! -- BGC |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
OK,
How about just the date? -- BGC "Jacob Skaria" wrote: To record the timestamp you cannot use a formula..You will have to use a VBA solution as mentioned in my earlier post. If this post helps click Yes --------------- Jacob Skaria "BC" wrote: Jacob, I tried this, closed, then reopened. File became read-only. Once I opened, the date changed again. Here's the formula I'm using. =if(m198=0,now()) -- BGC "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Count = 1 Then _ Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "BC" wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! -- BGC |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
There is no difference between just the date and the date/time... you cannot
"freeze" either one of them using just a formula... it cannot be done without using VB coding. -- Rick (MVP - Excel) "BC" wrote in message ... OK, How about just the date? -- BGC "Jacob Skaria" wrote: To record the timestamp you cannot use a formula..You will have to use a VBA solution as mentioned in my earlier post. If this post helps click Yes --------------- Jacob Skaria "BC" wrote: Jacob, I tried this, closed, then reopened. File became read-only. Once I opened, the date changed again. Here's the formula I'm using. =if(m198=0,now()) -- BGC "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Count = 1 Then _ Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "BC" wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! -- BGC |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
A bit of programming would work. Put this code in the sheet module
behind the workbook: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("B1").Calculate End If End Sub --JP On Sep 24, 1:56*pm, BC wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. *I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! -- BGC |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
JP,
It didn't work. Closed and reopen and the time updated! -- BGC "JP" wrote: A bit of programming would work. Put this code in the sheet module behind the workbook: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("B1").Calculate End If End Sub --JP On Sep 24, 1:56 pm, BC wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! -- BGC |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
Ugh, you could have mentioned earlier that it needed to stay the same
even if the workbook closed. In that case, you'll probably need to write more event handlers. --JP On Sep 24, 2:46*pm, BC wrote: JP, It didn't work. *Closed and reopen and the time updated! -- BGC "JP" wrote: A bit of programming would work. Put this code in the sheet module behind the workbook: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then * Range("B1").Calculate End If End Sub --JP |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
To enter a static date/time you can do this manually.
While holding CTRL key hit semi-colon then spacebar then SHIFT + semi-colon. To do this via VBA code Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("B" & n).Value = Format(Now, "mm-dd-yyyy hh:mm:ss") End If End If enditall: End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste to that module. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Thu, 24 Sep 2009 10:56:01 -0700, BC wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
Gord,
This didn't work either. I held the time, but once I closed and reopened, the time updated! -- BGC "Gord Dibben" wrote: To enter a static date/time you can do this manually. While holding CTRL key hit semi-colon then spacebar then SHIFT + semi-colon. To do this via VBA code Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("B" & n).Value = Format(Now, "mm-dd-yyyy hh:mm:ss") End If End If enditall: End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste to that module. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Thu, 24 Sep 2009 10:56:01 -0700, BC wrote: Hi all, I want cell b1 to display the current date and time based on cell a1's entry. I want the date to remain constant and not update unless a1 has been changed. I tried this and the date and time changes to current: =if(a1=0,now()) Help! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date function
Do not use a formula in B1.
Let the code enter the date/time for you. Read ALL my instructions on where to place the code. Gord On Thu, 24 Sep 2009 11:49:02 -0700, BC wrote: Gord, This didn't work either. I held the time, but once I closed and reopened, the time updated! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difference betwen Excel Date () Function and System Date | Excel Worksheet Functions | |||
| Difference System date and Excel Date function | Excel Worksheet Functions | |||
| Date function in ACCRINTM requires date format not available | Excel Worksheet Functions | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
![BC[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
