Enabling date columns to be incorporated into formulas

Sylvia Kay

I have built a milestone report. To keep it current, I am asking my team to update 7 columns (for 7 milestones) every week in the weekly updates sheet, so I can track.

In my sheet summary I am then pulling the most current values from the weekly update sheet. It is an Index Match formula, but it only can pull the date when I make this a text field. it will produce an error when the same column is configured as a date column.

Can you fix that?

Paul Newcome

Are you able to provide some screenshots for context?

Sylvia Kay

Hi Paul, this is the element with high level milestones rom my dashboard. It is the metric widget. The metrics come from my Sheet Summary, that grabs always the dates from the most 'current' status update using this formula:

=IFERROR(INDEX({02. Status Updates | SMP2024-0005 Milestone 5 GNG}, MATCH("Current", {Status Type}, 0)), 0)

The status update is also a sheet looking like this:

Here I would love to use the date columns instead of a text field (for consistency mainly)

However, when I change it, the above formula in my sheet summary does not return anything.

Best, Sylvia Kay

Paul Newcome

Where exactly is the word "Current" that you are matching on, and how is it being populated?

Sylvia Kay

Current is determined as a column formula:

=IF([Satus Date]@row = MAX([Status Date]:[Status Date]), "Current", "Past")

When I enter a new row with a newer date, this column will relabel the older line as "Past" and label the newer line as "Current". The columns are hidden.

Sylvia

Paul Newcome

The MAX function will not work with text values. It will only work with numbers and dates. If it is working with the Status Date column, are you sure it is not set as a date type column?

Sylvia Kay

The Current is working with a different column, which is a date column. In it we document the date of the weekly update only.

Milestones are different columns, here, like to pull always the value when the line is determined "current", using Index/Match. This is where the formula does not work when it is a date column.

Sylvia Kay

Maybe you have a different solution for what I am trying to do? Happy to show you live on a screen share.