use CTRL key inside a formula

[Date Less]

use CTRL key + ; inside a formula to put the static date in B2 If a2 = YES

if(a2="yes",today(),"blank") the date changes I want to replace today() with
CTRL ;

 

[Gord Dibben]

Not inside a formula.

What do you want in B2?

If today A2 equals "Yes" you want the date in B2?

If tomorrow A2 <> "Yes" what do you want to see in B2.........nothing?

If next Thursday A2 goes back to "Yes" should value of B2 change to that date?

I think you will have to resort to event code.


Gord Dibben MS Excel MVP

 

[Date Less]

Not inside a formula.

What do you want in B2?

If today A2 equals "Yes" you want the date in B2?

If tomorrow A2 <> "Yes" what do you want to see in B2.........nothing?

If next Thursday A2 goes back to "Yes" should value of B2 change to that date?

I think you will have to resort to event code.


Gord Dibben MS Excel MVP



The formula will be in b2. I have a drop down box in a2 'yes and no' if the user chooses yes then I want the static date in b2 if the user chooses no the I will leave b2 blank. I did use Today() last week on Friday the 11th and today when I opened the XLS document the date changed to the 14th. The research I have found so far is that <CTRL> + ; puts in a static date.

 

[Gary''s Student]

You can use CNTRL-;
but in a diferent way. Begin by typing:
=IF(A1="yes",DATEVALUE("

and then touch CNTRL-;

and complete the line with:

"),"blank")

The full line will look like:

=IF(A1="yes",DATEVALUE("4/15/2008"),"blank")

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A2")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value
Case "Yes"
Target.Offset(0, 1).Value = Format(Date, "dd/mm/yyyy")
Case "No"
Target.Offset(0, 1).Value = ""
End Select
endit:
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.

Alt + q to go back to the Excel window.

Select Yes ot No from dropdown in A2


Gord

 

[Date Less]

Thank you Gord that works really well I was able to take that advice and
change it to the A column and post the date in the B column and also the time
in the C column with the code.

Date less

 

[Gord Dibben]

Good to hear you're moving along.

Gord

Thank you Gord that works really well I was able to take that advice and
change it to the A column and post the date in the B column and also the time
in the C column with the code.

Date less