Need help with Date formula....

[Guest]

I have a spreadsheet that is updated daily during the week, so when I come in
on Monday, I have to fill in information for Friday, Saturday and Sunday. I
only do one spreadsheet at a time, then e-mail to the powers that be. At the
top of the spreadsheet in A3, I have the date for the day after the dayend
date that was run. So for instance, Friday's dayend spreadsheet I am filling
in will have Saturday's date in cell A3. Saturday's dayend sheet will have
Sunday's date, and so on. When I'm here during the week, I put in today's
date for yesterday's dayend. I have calculations based on the date that is
in A3 elsewhere in the spreadsheet, but every day I fill the sheet
information in, I have to remember to change the date. Column A has the
numbered days of the month. I need a formula that will auto calculate the
correct date in cell A3 if contents has been entered in range B8:B38. See
below for example. I hope this isn't as confusing as it sounds as I'm
reading back.

7/08/2007-->cell A3

A B
Day Admits
1 12
2 7
3 5
4 8
5 14
6 16
7 6
This example would is being done on Monday July 9th, I entered information
into spreadsheet for July 7th, so date on the spreadsheet should be July 8th.
The calculation I need is when data is entered into column B in the last
cell, will put the correct date in A3. Didn't know if there was some way to
use the A column numbers to do that or not? Say if column B has data entered
into the range in the last cell, then to take the first day of the month plus
the number in column A next to the cell where the data is entered and add
one? Any help would be greatly appreciated!!!

 

[Guest]

You can use a worksheet change function.

go to tabb on bottom of worksheet (normally sheet 1) and right click then
select view code. Paste code below into correct sheet. worksheet change
only works on the sheet correcsponding to the sheet you need changed.
Installing on sheet 1 will only work on sheett 1.

Sub worksheet_change(ByVal Target As Range)

If Target.Column = 2 And _
Target.Row >= 8 And _
Target.Row <= 38 Then


Cells(3, 1).Value = Now

End If

End Sub

 

[Guest]

perfect perfect perfect!!! Thanks so much Joel, worked like a charm! I
changed my system clock to test it!!!!

 

[Guest]

Joel,

The code you gave me worked during the week, but now I have passed a weekend
and it is giving me today's date when I try to do Friday's Dayend, should be
showing the 14th when I enter the 14th's information in column B??? Any ideas?

Thanks!!!
Tasha