Time/Date Stamp based on Boolean Value, Formula only.

[Spyclown]

Hi Folks,

I have a table with upwards of 1000 entries on it and multiple people will be working off this file (not at once).
The idea is simple, you check a check box and it adds a time stamp into the "Sent Date" column. Each box is linked to the neighboring cell.
However using the NOW() or TODAY() functions are volatile and refresh when a new entry is made or the workbook is closed and reopened. I have gotten close using IF commands, but am always faced with errors.

Some users are using Excel online and trying not to incorporate macros.

Ideas?

 

[Spyclown]

I found what seems to work. : =IF(E5,IF(F5="",NOW(),F5),"") Where as E5 is the Boolean Value and F5 is the stamp. This only works if iterative calculations is enabled.

It's not as clean as I'd like and not using the NOW function as intended, but it's functional! You click the check box and populates a static time/date stamp.

 

[Fluff]

Have you tried that with xl online? As I'm not sure that iterative calculations exist in the online version.

 

[Spyclown]

@Fluff, your right, Check boxes don't work on XL online.. doh, I overlooked that. Confirmed the formula does works online, I'll have to incorporate a different mechanism other than a check box.

 

[Fluff]

You may just have to put an "X" (or something similar) into the cell instead of the checkbox, or just get users to enter the date.

 

[Spyclown]

That's right on track of what I was thinking. trigger a stamp when a cell is changed. Thinking of this: =IF(A2<>"",IF(B2="",NOW(),B2),"")

 

[Fluff]

Why not just get them to enter the date?

 

[Spyclown]

The more work you give the users, the less likely it's going to happen.... lol

 

[Fluff]

They can easily enter today's date using Ctrl + semi-colon
I can't see how that's any more work than entering an X (or similar) into a different cell