Freezing a NOW() date...

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Good afternoon!

I have a form style spreadsheet that allows me to select
a "yes/no" radio button for approvals. The results "0
(none), 1 (yes) or 2 (no)" are displayed in cell D53. I
would like to generate the current date if "yes" or "no"
is selected, however, I need that date to remain static
once it has been entered.

I would be eternally grateful for any assistance with this
(even if the answer is; "it can't be done")! My current
formula is defined below... Many thanks in advance for
any insight!!!

=IF((D53=0),"",IF(OR(D53=1,2),NOW()))

All the best,

Michael
 
-----Original Message-----
Good afternoon!

I have a form style spreadsheet that allows me to select
a "yes/no" radio button for approvals. The results "0
(none), 1 (yes) or 2 (no)" are displayed in cell D53. I
would like to generate the current date if "yes" or "no"
is selected, however, I need that date to remain static
once it has been entered.

I would be eternally grateful for any assistance with this
(even if the answer is; "it can't be done")! My current
formula is defined below... Many thanks in advance for
any insight!!!

=IF((D53=0),"",IF(OR(D53=1,2),NOW()))

All the best,

Michael
.
Click to expand...

Instead of using the now function create your own function
like this:

Function test(val)
If val = 0 Then
test = ""
ElseIf val = 1 Then
test = Time
ElseIf val = 2 Then
test = Time
End If



End Function

then you enter it =test(D54). Format the cell for time
and it should remain static.

HTH
Jim
 
Jim,

First, let me say THANK YOU VERY MUCH for your
suggestion!!! I am grateful for your very valuable time.

With regard to your suggestion, I am unsure if it makes a
difference (I am assuming so), but I am using EXCEL 97 and
I cannot seem to get Excel to understand the "ElseIf"
statements, therefore, I am unable to progress with your
recommendation. (In my most humble voice), Any thoughts?

My sincere apologies for not stipulating this earlier...

Very best,

Michael
 
Hi Michael,

I used Jim's function and it worked fine. I'm sure it is not because I
have Excel 2002, 97 will understand ElseIf.

Did you put the function in a module and or the sheet code area? Works fine
for me in a Module.

I changed Time to Date as I think that's what you wanted.

HTH
Regards,
Howard
 
Howard,

THANK YOU, THANK YOU, THANK YOU!!! Working as described!
I did forget to copy "end function", not sure if that was
the problem, but it appears to be working fine...

I am very grateful for your follow-up!

Warmest regards,

Michael
 
Dear Gord,

You are correct, I did omit the "end function" comment,
and it does appear to be working... I will wait unitl
tomorrow to see if the date actually freezes, but the cell
is doing what I dreamed it would!

My sincere thanks for taking the time to test this and
provide me with your comments, I am deeptly grateful!!!

Warmest regards,

Michael
 
-----Original Message-----
Dear Gord,

You are correct, I did omit the "end function" comment,
and it does appear to be working... I will wait unitl
tomorrow to see if the date actually freezes, but the cell
is doing what I dreamed it would!

My sincere thanks for taking the time to test this and
provide me with your comments, I am deeptly grateful!!!

Warmest regards,

Michael
D53.
.
Click to expand...

Glad it works. Let me know how it works for you.

Jim
 
Back
Top