Latest Date Formula using multiple date columns

Platform discussions
, ,
1

Imagine there are multiple date columns on a board, and you need a column that shows the latest date considering all these date columns. I didn’t find anything regarding this, but some posts suggest that it’s not possible. This is somewhat true. This is not like an Excel sheet formula in any way. It means that you can’t use the MAX or MIN formulas as in Excel. However, there is a trick to do so.

The trick is to use the DATEVALUE formula to change the format of the date into a 5-digit value that Monday can recognize. In this way, Monday recognizes these new values as numbers, and so you can apply the MIN or MAX formulas. After finding the maximum or minimum values in the multiple date columns, you can use the FORMAT_DATE formula to turn the outcome (5-digit value) into an actual date format.

In my case, as I am managing high-level and low-level boards for a Medical Clinic specialized in mental health assessments, I need to have a column that can show the latest date between multiple date columns assigned to different parts and phases of the ongoing assessments to predict the number of reports that must be prepared and follow-up sessions. By using this, I can manage the distribution of workloads among different teams and different times of the month.

For your reference, you can use the following formula to find the latest date between multiple date columns.

FORMAT_DATE(Max(datevalue({date_column#1}),datevalue({date_column#2}),datevalue({date_column#3}),datevalue({date_column#4}),datevalue({date_column#5}))+1) "

**

2 Likes
2

Thanks so much for sharing @amirmoa! This will be a big help to our community :blush:

3

This is very helpful. However, how do I pull the latest date only for a task which is not completed yet. For the months/dates where the task is done, I do not want those date pulled.

For e.g., We have a recurring task that needs to be done every month. For this, we have created 12 columns for 12 months along with the status for each month. If the status for February is “Done”, but the status for all the months after March is pending, how do I pull in the latest date as March and not December.