Populate cell with current date and time

Bruce Case

Seems like a simple thing, but. . . . . .

Within a row, if a specific checkbox is checked, I want to capture the current date/time in a cell, that will not change. In short, I want a column that looks like Created Date and Modified Date, but is a date/time that is triggered and populated based on a condition.

I know I can get the current date =TODAY()

But can't find one to get the TIME

While I would like it to be in a single cell, I could probably work with it if Date was in one and Time was in another.

Kerry St. Thomas

You can look at the individual checkbox cell's history, but that doesn't really lend itself to scalability. Automations only record a DATE, which obviously won't work for your use case. The workaround I've used in the one case I had to record time is simliar to described here - it isn't pretty, but it works. I'd be delighted if anyone else can offer something that's a bit more elegant than this trashfire. 😀

1. The sheet on which this checkbox appears will need to have some kind of a unique identifier for each row. If you already have something you can use (like an order number or an employee ID or something that shouldn't be repeated on the sheet EVER), great. If not, add an Autonumber column.

2. Create a COPY ROW automation that is triggered when your box is checked. Copy that row to a new sheet whose entire purpose is to create this timestamp. On that NEW sheet, be certain a Created Date column exists. (If it already does on your main sheet, you'll need to test and possibly play with column names and/or types - you're trying to capture when the row is created on the NEW sheet and not the original sheet.)

3. On the NEW sheet, split the date and time of the Created column into separate cells with Text/Number type - use formulas like =LEFT(Created@row, FIND(" ",Created@row)-1) to accomplish this. Only column type Text/Number will retain the timestamp, so you're converting the data type by doing this split.

4. You'll then be able to look up that new "timestamp" cell using your unique identifier from #1 to pull the time over. You can use the TIME formula to do whatever calculations you need to.