Formulas using dates, pls help!

[saxter]

Hi all,

A newbie here. I'm putting together a spreadsheet to track variou
budgets. I am stumped. What I want to do is...

I have two columns of data - projected budget and actual budget. (ie
am tracking actual funds committed vs actually invoiced). I have thes
in two columns. Under the 'invoiced?' column I have the cells with th
default "N", which I will change to "Y" when an invoice is received an
paid. I have used SUMIF to sum the 'Invoiced?' column total as a "N" i
turned into a "Y". Well heres the question.

I then want a third column next to the 'invoiced?' column that wil
enter the actual date that the "N" is turned into a "Y". I have bee
playing around with the IF formula and trying to manipulate the TODA
formula to no result. So again I'm trying to have three columns for m
budgets. The first is 'Projected', the second is 'actual', and I want
third that will show todays date when the cell is changed from th
default "N" to a "Y"

Thanks in advance and i HOPE this makes sense. I have attached
spreadsheet to demonstrate what I'm after

Attachment filename: insight budget test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=43456

 

[Norman Harker]

Hi Saxter!

Since you are manually changing from N to Y you can input the current
system date very quickly using Ctrl+;



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

In addition to Norman
another way would be to use the worksheet_change event. Put the
following code in your worksheet module:

------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Intersect(.Cells, Range("B1:B100")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target
If .Value = "Y" Then
Application.EnableEvents = False
.Offset(0,1).NumberFormat = "dd mmm yyyy"
.Offset(0,1).value = now
end if
End With
Application.EnableEvents = True
End Sub

 

[saxter]

Thanks Norman "he of the hot keys"

Question...will this date change or stay on the day that it wa
entered. If it changes that isn't really what I'm after...thanks again

 

[Frank Kabel]

Hi
the date won't change

Frank

 

[saxter]

Thanks Frank and Norman, working a treat cheers