Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use CTRL key inside a formula
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 ; |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use CTRL key inside a formula
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 On Mon, 14 Apr 2008 16:03:00 -0700, Date Less wrote: 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 ; |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use CTRL key inside a formula
"Gord Dibben" wrote: 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 On Mon, 14 Apr 2008 16:03:00 -0700, Date Less wrote: 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 ; 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use CTRL key inside a formula
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") -- Gary''s Student - gsnu200779 "Date Less" wrote: 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 ; |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use CTRL key inside a formula
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 On Mon, 14 Apr 2008 18:01:01 -0700, Date Less wrote: "Gord Dibben" wrote: 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 On Mon, 14 Apr 2008 16:03:00 -0700, Date Less wrote: 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 ; 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use CTRL key inside a formula
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" wrote: 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 On Mon, 14 Apr 2008 18:01:01 -0700, Date Less wrote: "Gord Dibben" wrote: 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 On Mon, 14 Apr 2008 16:03:00 -0700, Date Less wrote: 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 ; 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use CTRL key inside a formula
Good to hear you're moving along.
Gord On Tue, 15 Apr 2008 14:38:02 -0700, Date Less wrote: 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" wrote: 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 On Mon, 14 Apr 2008 18:01:01 -0700, Date Less wrote: "Gord Dibben" wrote: 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 On Mon, 14 Apr 2008 16:03:00 -0700, Date Less wrote: 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 ; 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use CTRL key inside a macro IE CTRL + ; | Excel Worksheet Functions | |||
Anyone having Ctrl+C / Ctrl+V problems in E2007? | Excel Discussion (Misc queries) | |||
How to forbid ctrl+c and ctrl+X in sheet? | Setting up and Configuration of Excel | |||
when I use Ctrl+D to copy a formula, it does not compute right. | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |