Repost - Autofill today's date Everyday

[AM]

Some one tried to help me with this, but did not worked, so sorry for
reposting

My Original Message
I would like to see today's date in
column A everyday in new cell, maintaining previous cell with yesteray's date
and previous before previous cell with day before yesterday's date and so on.
So every weekday you see today's date in the cell.
e.g

08/20/2007
08/21/2007
08/22/2007 Today
Will show up tomorrow as 8/23/2007
Will show up Friday as 8/24/2007

Will be one blank row for saturday & Sunday
Will show up Monday as 8/27/2007

Thanks
AM


The answer was

Dim mLastRow As Integer

Dim mdate As Date
mdate = Date
If Weekday(mdate) > 1 Then
mLastRow = Range("A65000").End(xlUp).Row + 2
Else
mLastRow = Range("A65000").End(xlUp).Row + 1
Range("A" & mLastRow).Value = mdate
End If

This will automatically check for saturday and sunday and will skip a row
while inseting new date. I have tested this.
Hope this helps!!
But for some reason ot does not work for me, Any further help would be
greatly appriciated.

Thanks
AM

 

[Zone]

You could do this with a macro, but how you implement it would depend on
when you want to put the date in
- Every time the file is opened?
- Each time the sheet is displayed?
- When the sheet calculates?
- When a button is pushed?
James

 

[AM]

I want to do it when any value is entered in col B

Thanks

 

[I_am_fitz]

I want to do it when any value is entered in col B

Thanks






- Show quoted text -

Maybe the TODAY(). I use this to calculate age based on birthdate
everytime the sheet is opened the age calcualtes correctly.

example formula =datedif(A1,today(),"y")

hope this helps.

 

[Zone]

AM,
1. It doesn't matter which row in which the user is typing in column B?
2.What if the last cell in column A already contains the current date?
James

 

[Rick Rothstein \(MVP - VB\)]

If I understand your question correctly, I think this VBA code will work for
you...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
With Me.Cells(Rows.Count, 1).End(xlUp)
If .Value <> Date Then .Offset(1, 0).Value = Date
End With
End If
End Sub

It will place today's date in the next blank row of column A whenever a
change is made (anywhere) in column B provided the last filled row in column
A does not contain today's date already (this prevents multiple entries of
today's date for multiple changes in column B within the same day).

Rick

 

[AM]

There will be only one entry per day and I want to use this macro or
something else and lock the cell down. This os for weekday only.
This macro can also be triggered at opening of file.

Thanks
A

 

[Zone]

AM, see if this does what you want. Copy this code, right-click on the
sheet tab, click on View Code and paste the code in there. Presumes row 1
is a heading row. James

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDate As Date
ActiveSheet.Unprotect Password:="abc"
Cells.Locked = False
Columns(1).Locked = True
Select Case WorksheetFunction.Weekday(Now)
Case 2 To 6: myDate = Now
Case 1: myDate = Now + 1
Case 7: myDate = Now + 2
End Select
With Target
If .Column = 2 Then
Cells(Rows.Count, 1).End(xlUp).Offset(1) = myDate
End If
End With
ActiveSheet.Protect Password:="abc"
End Sub

 

[AM]

Both the code works and will do what I need,
Thanks a lot to all of you for the help. This group is great.

Best,
AM