Displaying Date of Most Recent Update on Dashboard

System

This discussion was created from comments split from: Sheets Last Modified.

Chris Rea

hoping to get an update on this. searching through google and the community hasn't brought anything up.

I have multiple sheets that are displaying data on a single dashboard. I'd like to display when the most recent update to any particular sheet/report/etc is updated.

This formula: =MAX({Thermwood Utilization - 2020 Data - Created}) + ""

currently produces: 6/22/20 7:35 AM

This formula: =MAX({BG Flat Utilization - 2020 Data - Created}) + ""

currently produces: 6/23/20 1:57 PM

Unfortunately the formula: =MAX([Column8]2, [Column9]2) + ""

currently produces: 0

I need the time to be displayed, not just the date. so removing the +"" and changing the column properties to date instead of text/number will not work for me.

has anyone found a solution for this?

Paul Newcome

We can use MID statements to create a string that shows the data as "yymmddhhmm" and convert that to a numerical value. Then you can run your final MAX on this column.

So the "yy" portion would be...

=MID([Column8]@row, FIND(" ", [Column8]@row) - 2, 2)

The "mm" portion (month):

=IF(VALUE(LEFT([Column8]@row, FIND("/", [Column8]@row) - 1)) < 10, "0") + LEFT([Column8]@row, FIND("/", [Column8]@row) - 1)

The "dd" portion we have two options. You didn't provide an example of a date with single digit day.

Option 1 (single digit day displayed as "6/1/20"):

=IF(VALUE(MID([Column8]@row, FIND("/", [Column8]@row) + 1, FIND("/", [Column8]@row, FIND("/", [Column8]@row) + 1) - (FIND("/", [Column8]@row) + 1))) < 10, MID([Column8]@row, FIND("/", [Column8]@row) + 1, FIND("/", [Column8]@row, FIND("/", [Column8]@row) + 1) - (FIND("/", [Column8]@row) + 1)))

Option 2 (single digit day displayed as "6/01/20"):

=MID([Column8]@row, FIND("/", [Column8]@row) + 1, FIND("/", [Column8]@row, FIND("/", [Column8]@row) + 1) - (FIND("/", [Column8]@row) + 1))

The "hh" portion:

=IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) < 10, "0") + VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) + IF(FIND("P", [Column8]@row) > 0, IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) <> 12, 12), IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) = 12, -12)) + ""

And finally the "mm" portion (minutes):

=MID([Column8]@row, FIND(":", [Column8]@row) + 1, 2)

Then we put it all into one long string...

=MID([Column8]@row, FIND(" ", [Column8]@row) - 2, 2) + IF(VALUE(LEFT([Column8]@row, FIND("/", [Column8]@row) - 1)) < 10, "0") + LEFT([Column8]@row, FIND("/", [Column8]@row) - 1) + appropriate_dd_formula + =IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) < 10, "0") + VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) + IF(FIND("P", [Column8]@row) > 0, IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) <> 12, 12), IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) = 12, -12)) + "" + MID([Column8]@row, FIND(":", [Column8]@row) + 1, 2)

And the last step is to wrap the whole thing in a VALUE function so that it is an actual number that can be used in you MAX function (just don't forget to plug in the appropriate "dd" formula in the bold portion).

=VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) - 2, 2) + IF(VALUE(LEFT([Column8]@row, FIND("/", [Column8]@row) - 1)) < 10, "0") + LEFT([Column8]@row, FIND("/", [Column8]@row) - 1) + appropriate_dd_formula + =IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) < 10, "0") + VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) + IF(FIND("P", [Column8]@row) > 0, IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) <> 12, 12), IF(VALUE(MID([Column8]@row, FIND(" ", [Column8]@row) + 1, FIND(":", [Column8]@row) - (FIND(" ", [Column8]@row) + 1))) = 12, -12)) + "" + MID([Column8]@row, FIND(":", [Column8]@row) + 1, 2))