Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
input date as date in formula
I want to place a "C" for canceled in a certain column, and based on the date that the "C" was entered, establish the status for the contract. For example, if it was entered today()-7, it would be a "New Cancellation", else "Canceled"
Is this possible? TIA, Jill. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
input date as date in formula
Thanks for your response!
I was actually looking for the vba code to calculate from the date the "C" was entered into the column. I didn't want the user to have to input the date, rather for Excel to calculate it automatically. Thanks! Jill. "sebastienm" wrote: Hi Jill 1. Data Entry Do you need help on the Status formula or on the data entry too? For data entry: Would the user enter both the C and the date when the C was entered? If you want the date to be entered automatically, you need vba code 2. Status formula Assuming C is entered in column A, the date in column B. For the status, assuming row 2: =IF( C2="C" , IF( (Today()-B2)<7 , "New Cancel", "Old Cancel") , "No Cancel") or based on how you column B2 is formatted, maybe use the Datavalue() function: =IF( C2="C" , IF( (Today()-B2)<7 , "New Cancel", "Old Cancel") , "No Cancel") -- Regards, Sébastien |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
input date as date in formula
Hi Jill,
The following event macro has to be placed in the corresponding sheet code module. It assumes the C is entered in column rgSEntry (here, A:A. If not, change the Set RgSEntry line). Anytime a cell is changed in column rgSEntry (A:A), it writes the current date to the cell on its right (here, B:B). '------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rgSEntry As Range Dim Rg As Range Dim cell As Range Set rgSEntry = Range("A:A") '<---- change here 'column where the C is placed Application.EnableEvents = False Set Rg = Application.Intersect(rgSEntry, Target) If Not Rg Is Nothing Then Rg.Offset(0, 1).Value = Date 'Write data one column on the right End If Application.EnableEvents = True End Sub '------------------------------------------------------------------ -- Regards, Sébastien |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
input date as date in formula
ok... since you did such a great job helping me with that problem... to take it a step further...
when i delete the "C," the date in the adjacent column remains. how do i delete that date automatically if the "C" is deleted? thanks! jill. "sebastienm" wrote: Hi Jill, The following event macro has to be placed in the corresponding sheet code module. It assumes the C is entered in column rgSEntry (here, A:A. If not, change the Set RgSEntry line). Anytime a cell is changed in column rgSEntry (A:A), it writes the current date to the cell on its right (here, B:B). '------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rgSEntry As Range Dim Rg As Range Dim cell As Range Set rgSEntry = Range("A:A") '<---- change here 'column where the C is placed Application.EnableEvents = False Set Rg = Application.Intersect(rgSEntry, Target) If Not Rg Is Nothing Then Rg.Offset(0, 1).Value = Date 'Write data one column on the right End If Application.EnableEvents = True End Sub '------------------------------------------------------------------ -- Regards, Sébastien |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
input date as date in formula
since you helped me so graciously... i wonder if i could take it a step further...
when i delete the "C," the date in the adjacent column remains. how would i delete that date automatically if the "C" is deleted? thanks! jill. "sebastienm" wrote: Hi Jill, The following event macro has to be placed in the corresponding sheet code module. It assumes the C is entered in column rgSEntry (here, A:A. If not, change the Set RgSEntry line). Anytime a cell is changed in column rgSEntry (A:A), it writes the current date to the cell on its right (here, B:B). '------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rgSEntry As Range Dim Rg As Range Dim cell As Range Set rgSEntry = Range("A:A") '<---- change here 'column where the C is placed Application.EnableEvents = False Set Rg = Application.Intersect(rgSEntry, Target) If Not Rg Is Nothing Then Rg.Offset(0, 1).Value = Date 'Write data one column on the right End If Application.EnableEvents = True End Sub '------------------------------------------------------------------ -- Regards, Sébastien |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How input today's date and keep that date | Excel Worksheet Functions | |||
input a date or update it based on date in another cell | New Users to Excel | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
date input | Excel Discussion (Misc queries) | |||
how do i make a cell date sensitive to execute a formula or input. | Excel Discussion (Misc queries) |