Conditional date and time

[RST Engineering]

Pick any three cells. Let's use the old familiar A1, B1, and C1. If
A1 is empty, I want B1 and C1 to be empty also. However, if A1 has
anything at all in it, I want the date and time that A1 was filled to
appear in B1 and C1 respectively. I've tried all permutations of the
built-in functions and can't find any combination that will do this.

Thoughts?

Thanks,

Jim

 

[isabelle]

hi,

=IF(ISBLANK(A1),NOW(),"")


--
isabelle



Le 2012-04-17 09:10, RST Engineering a écrit :

 

[isabelle]

also,

if you are looking for a date of an event already past you must use vba

--
isabelle



Le 2012-04-17 09:27, isabelle a écrit :

 

[Claus Busch]

Hi Jim,

Am Tue, 17 Apr 2012 06:10:23 -0700 schrieb RST Engineering:

Pick any three cells. Let's use the old familiar A1, B1, and C1. If
A1 is empty, I want B1 and C1 to be empty also. However, if A1 has
anything at all in it, I want the date and time that A1 was filled to
appear in B1 and C1 respectively. I've tried all permutations of the
built-in functions and can't find any combination that will do this.

DATE() and TIME() are volatile. With every new calculation the values
will be changed.
You have to try it with VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Or Target.Count > 1 _
Then Exit Sub

If Target <> "" Then
Target.Offset(0, 1) = Date
Target.Offset(0, 2) = Time
Else
Target.Offset(0, 1) = ""
Target.Offset(0, 2) = ""
End If

End Sub


Regards
Claus Busch

 

[RST Engineering]

I perhaps did not state the problem as clearly as I should have.

I have a spreadsheet with a hundred student names. I want them to log
in when they come into the class and log out when they leave. I will
let them put any character they want into the "log me in" and "log me
out" cells and have Excel make a permanent record of when each student
logged in and each student logged out.

Another student logging in or out and having the spreadsheet
recalculate should not change the first student's times. Sorry for
not setting out the problem the first time.

Thanks,

Jim