Auto-populating the date of when another cell was changed.

Options

Hi,

I'm trying to amend a cell where I'd like to show the date the cell immediately above it was updated.

There's already a function in the cell of =JOIN(TODAY(0)) in the receiving cell, but this seems to show the date that any cell in the same column has been updated.

I already have a full column of Modified dates, which does show the data I'd like.

However, if I remove the formula and ask it to reference the cell with the correct information I get either an UNPARSEABLE or an INVALID COLUMN VALUE.

I appreciate that the screenshot shows the same dates, but that's because I've been trying to work it out.

I've looked at Automations but they all seem to reference rows, rather than specific cells.

Any ideas gratefully received.

Thanks

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/17/23 Answer ✓
    Options

    Hi @PM_Reeves

    =[Modified]4

    Would pull in the date from the Modified column on row 4. However, this is a date and can only be placed in a column formatted as Date type. Your Project Name isn't a Date type column. But you can convert the date into text so that you can display it in a Text column.

    The easiest way to change a date to text is to add something to the end of it like this:

    =[Modified]4+""

    (I've added a nothing to the end)

    This will now bring the date in as text. It brings the full value from the Modified column which includes the time. You can adjust the formula to include DATEONLY which will remove the time if you need to.

    =DATEONLY([Modified]4) + ""

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/17/23 Answer ✓
    Options

    Hi @PM_Reeves

    =[Modified]4

    Would pull in the date from the Modified column on row 4. However, this is a date and can only be placed in a column formatted as Date type. Your Project Name isn't a Date type column. But you can convert the date into text so that you can display it in a Text column.

    The easiest way to change a date to text is to add something to the end of it like this:

    =[Modified]4+""

    (I've added a nothing to the end)

    This will now bring the date in as text. It brings the full value from the Modified column which includes the time. You can adjust the formula to include DATEONLY which will remove the time if you need to.

    =DATEONLY([Modified]4) + ""

  • PM_Reeves
    PM_Reeves ✭✭✭✭
    Options

    Thanks @KPH,

    You're a star. Have today off :D

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Thanks @PM_Reeves Happy I could help! ⭐️