Form Submissions: Multiple Condition IF Formula Isolated to Most Recent Only

maria_NOMAD

Hello Friends!

I have a form that my field crew uses to check the condition of our machines every morning, which includes the most recent hour/meter reading. There are (5) machines that we own that I want to isolate the most recent reading into a dedicated cell in order to determine how soon the next service will be.

Also, I have a master sheet that tracks where each machine is located etc and I would like to link the dedicated cell with the most recent read on this sheet to a cell on that master sheet. (That I know how to do).

This is what my sheet looks like, also note that previously the submissions were being received at the bottom but I have changed the settings to receive at the top:

To my knowledge these are the conditions that need to be integrated:

• Restrict the reference to most recent submissions only (there is one per machine per day).
• Individually reference the (5) machine readings by their "Nomad" serial number.
• Copy the meter reading to the dedicated cell under the section "Hour Readings".

I've made attempts to figure this out, and conceptually I know what needs to be done but I'm certain I have the syntax wrong. In general, this is what I'm aiming for:

=IF(AND([Timestamp]:[Timestamp] = Today, [NOMAD Serial Number]:[NOMAD Serial Number] = "[Unique Serial # Here]", ….. then copy the number under Hour Meter Reading from that row to this dedicated cell.

As you can see, I need help :)

I appreciate the knowledge share!

jmyzk_cloudsmart_jp

Hi @maria_NOMAD

I added two helper columns;

[Most Recent & Today] =IF(Timestamp@row = TODAY([Date Adjust]@row), IF([Nomad Machine?]@row = "YES", IF(MAX(COLLECT(Timestamp:Timestamp, [Nomad Machine?]:[Nomad Machine?], "YES", SN:SN, SN@row)) = Timestamp@row, 1, 0), IF([Nomad Machine?]@row = "NO", IF(MAX(COLLECT(Timestamp:Timestamp, [Nomad Machine?]:[Nomad Machine?], "NO", SN:SN, SN@row)) = Timestamp@row, 1, 0))))

[SN] =IF([Nomad Machine?]@row = "YES", [NOMAD Serial Number]@row, IF([Nomad Machine?]@row = "NO", [Serial Number]@row))

The second one is just to make the first formula compact.

The first formula checks if the Timestamp is today. Then, if so, check if the max Timestamp with the same serial number is the same as the Timestamp@row, depending on whether the [Nomad Machin?] is Yes or No.

As I test with sample data, I find that my Timestamp shows today's date, and Timestamp@row = TODAY() becomes negative. Then, I remembered that in the Japan Timezone with UTC or GMT Offset + 9, though the Timestamp or Created column shows today's date when I calculate, it references the PST timezone's value.

So, the TODAY([Date Adjust]@row) is to adjust the phenomenon. (Diff# comes from the Sheet Summary)

[Time 24 format] =TIME(RIGHT(Timestamp@row, 8), 1)
[Date Adjust] =IF(VALUE(LEFT([Time 24 format]@row, 2)) < Diff#, -1, 0)

But if you are close to the Pacific Time zone, don't worry about it.

Time zones and system columns

System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

Pacific Time overrides

When changes are made to a sheet via automation, cell links, or cross-sheet formulas, the whole sheet is converted to Pacific Time. Automation services are hosted by service users with a Pacific Time setting. This means that TODAY() formulas and display system column display values may get updated if you're in a different time zone.

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones#toc-time-zones-and-system-columns

https://app.smartsheet.com/b/publish?EQBCT=ff52e31aad984669879df4d59787457a

At the master sheet, you can use the INDEX(MATCH() or JOIN(COLLECT() function using the [Most Recent & Today] to get the most recent value.

https://app.smartsheet.com/b/publish?EQBCT=dea6f9a5d21d42498cccd243a6dd731d

jmyzk_cloudsmart_jp

p/s

I've found a more straightforward way to determine it today, regardless of the UTC Offset values.

[is Today] =IF(LEFT(Timestamp@row, 8) = TODAY() + "", 1)

[Most Recent & Today 2] =IF([is Today]@row, IF([Nomad Machine?]@row = "YES", IF(MAX(COLLECT(Timestamp:Timestamp, [Nomad Machine?]:[Nomad Machine?], "YES", SN:SN, SN@row)) = Timestamp@row, 1, 0), IF([Nomad Machine?]@row = "NO", IF(MAX(COLLECT(Timestamp:Timestamp, [Nomad Machine?]:[Nomad Machine?], "NO", SN:SN, SN@row)) = Timestamp@row, 1, 0))))