Automated Timestamp

[sam810]

I’m in the process of making student attendance sheet for the college I work. I have the following formula in sheet 2 column C of a workbook to populate the name of the student who got a first warning for attendance shortage in sheet 1.

=IF('Section 1'!AK10="First Warning",'Section 1'!C10,"")

Where AK column is the status column that shows warning and C has the names of students. Sheet 1 is renamed as Section 1.

I need an automatic timestamp in column D of sheet 2 as the column C gets populated automatically.

Could you please help?

Thanks

 

[Luke M]

Right click on the sheet tab, view code, paste this in. Note that it currently checks range C2:C100, and gives a time and date value. If you just want the date, change the one word from "Now" to "Date".

Private Sub Worksheet_Calculate()


Application.EnableEvents = False

For Each c In Range("C2:C100") 'adjust as needed

If c.Value <> "" And c.Offset(0, 1).Value = "" Then


'If formula is displaying somthing now, but no time stamp

'has been input before, give timestamp

c.Offset(0, 1).Value = Now 'or, if you just want the date, use the word "Date"

End If

Next

Application.EnableEvents = True


End Sub

 

[sam810]

Thanks a lot. I tried it and it worked. Could I also request a little more help? In the same sheet 2, I also want the same time stamp in columns F and H automatically, as the Columns E & G gets populated like column C with names of second warning and third warning students from sheet 1. E and G have similar formula as C.So totally there will be 3 columns with the timestamp in sheet 2

Thank you in advance

 

[Luke M]

Private Sub Worksheet_Calculate()


Application.EnableEvents = False

For Each c In Range("C2:C100") 'adjust as needed

If c.Value <> "" And c.Offset(0, 1).Value = "" Then


'If formula is displaying somthing now, but no time stamp

'has been input before, give timestamp

c.Offset(0, 1).Value = Now 'or, if you just want the date, use the word "Date"

End If

Next


For Each c In Range("F2:F100") 'adjust as needed

If c.Value <> "" And c.Offset(0, 1).Value = "" Then


'If formula is displaying somthing now, but no time stamp

'has been input before, give timestamp

c.Offset(0, 1).Value = Now 'or, if you just want the date, use the word "Date"

End If

Next


For Each c In Range("H2:H100") 'adjust as needed

If c.Value <> "" And c.Offset(0, 1).Value = "" Then


'If formula is displaying somthing now, but no time stamp

'has been input before, give timestamp

c.Offset(0, 1).Value = Now 'or, if you just want the date, use the word "Date"

End If

Next

Application.EnableEvents = True


End Sub

 

[sam810]

Thank you. I tried your suggestion, but it's not giving the timestamp in columns F and H, when E and G are populated. It only gives for D, when C is populated. Maybe the code needs slight polishing.Would it be possible for you to look into this again?

I think you might need more input about my case.

E.g: The name of the student who gets First Warning in sheet 1, appears in column C of sheet 2. The same student, when he gets the second warning appears in column E but disappears from column C. the same student, when he gets a third warning appears in column G and he disappears from column E. The date and time the student receives the three warnings are very important and that is the objective behind this worksheet. The formula in E is =IF('Section 1'!AK10="Second Warning",'Section 1'!C10,""). Similarly is the formula in G: =IF('Section 1'!AK10="Third Warning",'Section 1'!C10,"").

Sorry for the trouble.

 

[Luke M]

Just need to change which range the code is looking at.


Private Sub Worksheet_Calculate()


Application.EnableEvents = False

For Each c In Range("C2:C100") 'adjust as needed

If c.Value <> "" And c.Offset(0, 1).Value = "" Then


'If formula is displaying somthing now, but no time stamp

'has been input before, give timestamp

c.Offset(0, 1).Value = Now 'or, if you just want the date, use the word "Date"

End If

Next


For Each c In Range("E2:E100") 'adjust as needed

If c.Value <> "" And c.Offset(0, 1).Value = "" Then


'If formula is displaying somthing now, but no time stamp

'has been input before, give timestamp

c.Offset(0, 1).Value = Now 'or, if you just want the date, use the word "Date"

End If

Next


For Each c In Range("G2:G100") 'adjust as needed

If c.Value <> "" And c.Offset(0, 1).Value = "" Then


'If formula is displaying somthing now, but no time stamp

'has been input before, give timestamp

c.Offset(0, 1).Value = Now 'or, if you just want the date, use the word "Date"

End If

Next

Application.EnableEvents = True


End Sub

 

[sam810]

Thank you very much.