If Cells not blank equal max date

Leah

I have a sheet with 7 columns that have dates (was checkboxes but could not get it to work) I want an 8th column to populate the max date from the 7 columns ONLY if all columns have a date entered. Basically I am tracking when people submit multiple documents, once all are submitted (i.e all dates entered) I issue them a certification beginning on that date. 

Genevieve P.

Hi Leah,

You can use a MAX formula to find out the largest date within a row. You will also want to use the @row function to make sure the formula is just looking at a row and not a larger reference table. For example:

=MAX([Date 1]@row:[Date 7]@row)

This will look across all 7 of my date columns in one specific row (which is what the @row signifies after the column names) and return the highest date. Please note that it will need to be entered in a date column type. 

Then, to only have this formula calculate if all the other 7 cells are Not Blank, you can create a nested IF formula that is an IF(AND(ISDATE formula. This will say “If this cell contains a date, and so does this one, and this one, and this one, etc, then return the Maximum date. Otherwise, return a blank cell.” I have broken it down by cell, so you can see what this looks like:

 

=IF(AND(ISDATE([Date 1]@row), 

ISDATE([Date 2]@row), 

ISDATE([Date 3]@row), 

ISDATE([Date 4]@row), 

ISDATE([Date 5]@row), 

ISDATE([Date 6]@row),

ISDATE([Date 7]@row)), 

Then, 

MAX([Date 1]@row:[Date 7]@row), "")

 

Note that you will need to change out my column names in bold (Date 1, etc) to be your column names for each date. Full example formula:

 

=IF(AND(ISDATE([Date 1]@row), ISDATE([Date 2]@row), ISDATE([Date 3]@row), ISDATE([Date 4]@row), ISDATE([Date 5]@row), ISDATE([Date 6]@row), ISDATE([Date 7]@row)), MAX([Date 1]@row:[Date 7]@row), "")

 

Cheers!

Jeffrey Homme

Genevieve - I followed the steps above but am not able to ge the max date to pull in from either the "Charter Approved" or "Schedule Approved" column. It just states "Blank" which should only happen if one of the dates are missing.

Are you able to spot the issue with my formula below?

Note - all three columns "Charter Approved", "Schedule Approved" and "Column 12" are Date Types.

Genevieve P.

Hi @Jeffrey Homme

I've tested your formula on my sheet and as long as those two values are dates it worked as expected. The reason you're getting "Blank" is because the values are somehow not being read as dates.

How are they being input? Is it from a formula, a form, or a different manual input? Could you possibly just test each cell individually, with this:

=IF(ISDATE([Charter Approved]@row), "Yes", "No")

=IF(ISDATE([Schedule Approved]@row), "Yes", "No")

This will let us know which of the columns is not being read as a date. From there we can look into it further.

You could also adjust the column properties to Restrict to date values only, but if you have any text or formulas in the column this won't work. If you're using a formula, we may be able to adjust it to ensure that the output is a date and not text.

Let me know what you find out!

Genevieve

Jeffrey Homme

Genevieve,

Thank you for the fast reply.

The data is pulled through a process of Formulas, Control Center, Reports/Pivot and Data Mesh, which is laid out below.

1. Dates are placed into a Project Sheet in Data columns which then pull into a Summary column via formulas on that same sheet (that summary column is a text field)
2. Those dates from the summary column are pulled into a sheet managed through Control Center that tracks all the PMOs projects. Those columns are text columns
3. I then ran a Report to filter only the projects I wanted to see
4. Then I use the PIVOT tool to pull the Project IDs from that Report
5. Finally I use DataMesh to pull in the other information from that initial Report. *This is the sheet I am trying to use the formulas in.

=IF(ISDATE([Charter Approved]@row), "Yes", "No") - Does not recognize dates

=IF(ISDATE([Schedule Approved]@row), "Yes", "No") - Recognize dates

I can't figure out why Charter Approved dates are not recognized but Schedule Approved is. I tested it on the sheet maintained via Control Center (#2) and the Charter Approved dates are not recognized there either.

Thoughts?

Genevieve P.

Hi @Jeffrey Homme

It sounds like when the dates for your Charter Approved column are brought over into the Text column and then back again, they're staying as text when the Data Mesh brings them in.

Did you try adjusting the column properties for the Charter Approved column to be date only?

Jeffrey Homme

Hey Genevieve,

I now have it working. I changed the tables in the Control Center sheet to dates which didnt fix the issue until after I changed the Datamesh to Overwrite Existing Data in Target Sheet(s). This fixed the issue and the formulas you shared on 10/27/29 now work.

What I dont understand is why the Schedule Approved column worked the whole time but the Charter Approved column did not until I made the changes above. Nothing in these two columns were different as far as I could tell (same column type, same exact formulas that fed into it). But it works now so I can move forward.

Thank you for your help Genevieve.

Genevieve P.

No problem! I'm glad you got it working... although I have to admit that I have no idea why the two columns were treated differently, either.

If you run into issues like this again, you may want to contact Support so they can work with you one-on-one and check into the actual sheets/data you're using more directly in a private channel.

Hope you have a great rest of your day!

Cheers,

Genevieve