datetime parse formula help

Hey ​@mflo,

I did not go in depth in your formula, but you’ll probably want to have an IF condition before checking if such fields are blank or not.
 

IF(
  AND(
    {Last Entered Patrol Start Date (from Patrol Reports Section 1)} = BLANK(),
    {Last Entered Patrol Start Date (from Patrol Reports Section 2)} = BLANK(),
    {Last Entered Patrol Start Date (from Patrol Reports Section 3)} = BLANK(),
    {Last Entered Patrol Start Date (from Patrol Reports Section 4)} = BLANK(),
    {Last Entered Patrol Start Date (from Patrol Reports Section 5)} = BLANK(),
    {Last Entered Patrol Start Date (from Patrol Reports Section 6)} = BLANK(),
    {Last Entered Patrol Start Date (from Patrol Reports Section 7)} = BLANK(),
    {Last Entered Patrol Start Date (from Patrol Reports Section 8)} = BLANK(),
    {Last Entered Patrol Start Date (from Patrol Reports Section 9)} = BLANK()
  ),
  BLANK(),
  DATETIME_PARSE(
    MAX(
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 1)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 1)}, 'x')), 0),
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 2)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 2)}, 'x')), 0),
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 3)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 3)}, 'x')), 0),
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 4)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 4)}, 'x')), 0),
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 5)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 5)}, 'x')), 0),
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 6)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 6)}, 'x')), 0),
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 7)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 7)}, 'x')), 0),
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 8)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 8)}, 'x')), 0),
      IF({Last Entered Patrol Start Date (from Patrol Reports Section 9)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 9)}, 'x')), 0)
    ),
    'x'
  )
)

 
Mike, Consultant @ Automatic Nation 
YouTube Channel 

Hm, I’m really curious about your base set up.  Each linked field (Patrol Reports Section 1, 2 etc) links to a different table, is that right?  If so, does each table have really different fields and that’s why they’re each their own table?  

You’ve probably already thought about this, but if their data’s all broadly similar, having them all in the same table and using a select field to designate which section they’re in might be cleaner and would let us use a rollup with MAX(values) on it instead

​@mflo 

I came here to say what Adam just posted above.

Based on your formula, it doesn’t seem like your database’s structure has been setup in the proper way.

It seems like you’ve setup your database like a spreadsheet app, but as a result of that, you are missing out on all the power & customizability & flexibility of using a database app.

What you really want to do is move all of your patrol reports into their own table (each report would be its own record in that table), and then link the master record (in your master table) to all of its associated patrol reports (from the reports table).

After you do that, you will be able to unlock all the power of using a database app, including the ability to use Rollup fields, embedded interface lists, no more need for complicated formulas, and much much more.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

​@TheTimeSavingCo  and ​@ScottWorld without going into too much detail this particular solution was set up this way on purpose for our particular business need (though I’m not the one that initially set it up this way) basically it’s one trail patrol report (one form submitted) but the patroller is covering multiple trail sections (up to 9 at a time) at one time. I’m linking that one activity back to our trail sections to derive the last time a trail section was looked at.  In most cases our bases are set up keeping relational db concepts in mind, this one is a bit wonky.

​@Mike_AutomaticN will try this and get back to you, was thinking I needed to wrap an If statement around it but couldn’t quite get it to work.  

Sure just let me know ​@mflo

Happy to help

​@Mike_AutomaticN it worked! thanks very much! 

Glad to hear that ​@mflo!!

If you need further help, feel free to grab a slot using this link.

Mike, Consultant @ Automatic Nation 
YouTube Channel