input date as date in formula

J

Jill

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.
 
S

sebastienm

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")
 
H

horinemj

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.
 
S

sebastienm

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
'------------------------------------------------------------------[/QUOTE][/QUOTE]
 
H

horinemj

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 said:
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
'------------------------------------------------------------------
Click to expand...
[/QUOTE][/QUOTE]
 
H

horinemj

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 said:
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
'------------------------------------------------------------------
Click to expand...
[/QUOTE][/QUOTE]
 
Top