Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and time keep disappearing
Hello,
I am trying to capture a date stamp in a cell when a certain option has been selected from a drop down menu. After searching on this forum i made use of Function DateAndTime() DateAndTime = Now End Function Essentially I have 2 options in my drop down list. "On Going" and "Complete" I have 2 further columns labelled Start Time and Finish Time. I am trying to capture a date stamp in each column using this formula in column f =IF(E3="On Going",DateAndTime(),"") and this formula in column g =IF(E3="Complete",DateAndTime(),"") when I select "On Going" from the drop down list the time is captured in column f perfectly. However when I select "Complete" it captures the time in column g but nulls the time previously captured in column f (when "On Going" was selected) as I have changed the value from the drop down list. Is there anyway that I can ensure the value captured in Column f stays? thanks in advance for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and time keep disappearing
http://www.mcgimpsey.com/excel/timestamp.html
-- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message ups.com... | Hello, | | I am trying to capture a date stamp in a cell when a certain option has | been selected from a drop down menu. After searching on this forum i | made use of | | Function DateAndTime() | DateAndTime = Now | End Function | | Essentially I have 2 options in my drop down list. "On Going" and | "Complete" | | I have 2 further columns labelled Start Time and Finish Time. I am | trying to capture a date stamp in each column using this formula in | column f | | =IF(E3="On Going",DateAndTime(),"") | | and this formula in column g | | =IF(E3="Complete",DateAndTime(),"") | | when I select "On Going" from the drop down list the time is captured | in column f perfectly. However when I select "Complete" it captures | the time in column g but nulls the time previously captured in column f | (when "On Going" was selected) as I have changed the value from the | drop down list. Is there anyway that I can ensure the value captured | in Column f stays? | | thanks in advance for any help. | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and time keep disappearing
The value in F is not "null", it just looks blank because of the "".
The actual value in F is FALSE because you have change from "ongoing" to "complete" Using your DateAndTime Function produces this. You may be better off with event code that produces a static date and time in the cells of F and G Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 5 Then n = Target.Row If Excel.Range("E" & n).Value = "on going" Then Target.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") ElseIf Excel.Range("E" & n).Value = "complete" Then Target.Offset(0, 2).Value = Format(Now, "dd mmm yyyy h:mm:ss") End If End If enditall: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On 4 Dec 2006 08:27:37 -0800, wrote: Hello, I am trying to capture a date stamp in a cell when a certain option has been selected from a drop down menu. After searching on this forum i made use of Function DateAndTime() DateAndTime = Now End Function Essentially I have 2 options in my drop down list. "On Going" and "Complete" I have 2 further columns labelled Start Time and Finish Time. I am trying to capture a date stamp in each column using this formula in column f =IF(E3="On Going",DateAndTime(),"") and this formula in column g =IF(E3="Complete",DateAndTime(),"") when I select "On Going" from the drop down list the time is captured in column f perfectly. However when I select "Complete" it captures the time in column g but nulls the time previously captured in column f (when "On Going" was selected) as I have changed the value from the drop down list. Is there anyway that I can ensure the value captured in Column f stays? thanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine Date & Time Cells | Excel Discussion (Misc queries) | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions |