Formula for Updated By and Updated Date Fields

Andrea Mayer

Hi all,

Question about a formula I am trying to get started. I want to make sure this is possible before going much further. I have ONE sheet that has four different reports pulling off of it. Each report has 5 lines that Project Leads can update. We are want to have two fields (in the sheet) that autopopulate the Name of the person and the date that updates are made to ANY one of the 5 lines in ANY one of the 4 reports. I know I can use an index match formula to help me pull the name from the login email address but want to make sure the other automation is possible first.

Any advice is welcome! TIA.

Andrea

Paul Newcome

Ah. Ok. In that case you are going to want to put the formulas into sheet summary fields as opposed to directly on the sheet itself. Formulas updating will change the Modified columns and will make for some frustrating issues.

First summary field to capture most recent date/time stamp would be:

=MAX([Modified Date]:[Modified Date]) + ""

Second formula to pull in the person would be

=INDEX([Modified By]:[Modified By], MATCH([Most Recent Date]#, [Modified Date]:[Modified Date], 0))

Paul Newcome

You wouldn't reference the summary field like that. You would wrap the MAX piece in the DATEONLY function (without the plus quote quote on the end).

=DATEONLY(MAX(..............))

Paul Newcome

That's what I used in my formula to indicate the sheet summary field that is housing the most recent date. The one populated by the MAX statement.

Paul Newcome

You shouldn't need an INDEX MATCH or any other formula for that matter. Just include the two system generated columns in the sheet.

Or do you mean you want to have the absolute most recent displayed in separate fields?

Andrea Mayer

Right - so ONE field for the name and ONE field for the date that will autopopulate the most updated name and date when any update is made to the entire sheet.

Paul Newcome

Ah. Ok. In that case you are going to want to put the formulas into sheet summary fields as opposed to directly on the sheet itself. Formulas updating will change the Modified columns and will make for some frustrating issues.

First summary field to capture most recent date/time stamp would be:

=MAX([Modified Date]:[Modified Date]) + ""

Second formula to pull in the person would be

=INDEX([Modified By]:[Modified By], MATCH([Most Recent Date]#, [Modified Date]:[Modified Date], 0))

Andrea Mayer

Thank you, Paul! This is so helpful.

I truly appreciate all your help in this community.

Andrea Mayer

@Paul Newcome another one for you - is there a way to change this from date and time to DATE ONLY when it's in the summary field?

Andrée Starå

Hi @Andrea Mayer

I hope you're well and safe!

Have you tried the DATEONLY function?

Would that work?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You could use the DATEONLY function to do this, but then it would break the match portion of the formula that reads from it. If you want to do a DATEONLY, I would suggest using a separate field for it so nothing else breaks.

Andrea Mayer

Thanks Andree and Paul!

I'm familiar with the DATEONLY function. Would the formula be added to a separate field in the summary panel or in the sheet itself? If it's another formula in the sheet summary, how do I reference the DATE cell in that formula if it's in the sheet summary?

Andrée Starå

@Andrea Mayer

Happy to help!

As Paul mentioned, I also recommend adding it to a separate field.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrea Mayer

Thank you again, @Andrée Starå .

When I create a new summary cell with the formula, I receive a formula message stating, "Invalid Data Type". It is a date field - do you know how I can get it to pull the date only?

Paul Newcome

You wouldn't reference the summary field like that. You would wrap the MAX piece in the DATEONLY function (without the plus quote quote on the end).

=DATEONLY(MAX(..............))

Andrea Mayer

Thank you @Paul Newcome!

For the other piece of data, I've tried the formula below but I'm receiving an #UNPARSEABLE error. Thoughts?

=INDEX([UPDATED BY]:[UPDATED BY], MATCH([Most Recent Date]#, [UPDATED DATE]:[UPDATED DATE], 0))

Is there another formula I should be using other than index match?

Paul Newcome

Make sure column and field names match what you are actually using in your sheet.

Andrea Mayer

Thanks, Paul. Is 'Most Recent Date' a field name? Or is that a required part of the formula?

Paul Newcome

That's what I used in my formula to indicate the sheet summary field that is housing the most recent date. The one populated by the MAX statement.