Using IF/AND in Formula - ISSUE

Platform discussions
1

Hello,

I am trying to create a formula where if the Completion Date column is blank AND today’s date is past the date in the Due Date column, the formula returns a value of 1. Otherwise, it returns a value of 0.

The formula I have tried is IF(AND({Completion Date} = “”, TODAY() > {Due Date}), 1, 0) but I’m only getting a return value of 0. Does anyone have any suggestions on an alternate formula that can potentially work?

2

Hi Erica,

There’s something weird about date comparisons on computers in general and certainly on monday as well.

The formula you can use is the following:

IF(
	AND(
    	{Completion Date}="",
        DATEVALUE({Due Date})<DATEVALUE(FORMAT_DATE(TODAY()))
    ), 
   	0, 
    1
)

As you can see I’m using the DATEVALUE function. This function returns the number of days between January 1st 1900 and the given date which allows for better date comparison. The FORMAT_DATE function around TODAY on its end is used to avoid any timezone issues that might occur if users in different timezones us the same board.

As you can see in the following screenshot the formula should work as expected:
image

Hope this helps!

Best,

Maarten

3

Hi Maarten! Thanks so much for your help. I tried out this formula, and unfortunately I am still getting a value of “1” even if the completion date column is not empty. I do appreciate the comment about date comparisons though - I’ll have to keep checking it out with that in mind!

4

Hi Erica, I think this should work for you. If you don’t want it to flag when Due Date = Today, you can just change the number after the < symbol:

IF(AND(DAYS({Due Date},TODAY())<0,{Completion Date}=“”),1,0)

image

1 Like
5

Hi everyone, I wanted to provide an update that the monday team was able to help with a solution!

The final formula is:
IF(AND({Completion Date}=“”, DATEVALUE(FORMAT_DATE(TODAY())) > DATEVALUE({Due Date})), 1, 0)

The reasoning: We can not compare dates only using <>. This only works for numbers. The solution to this puzzle is a hidden function in monday, called DATEVALUE . Datevalue assigns a unique numerical value to every date, and therefore allows comparing dates to each other. The general format of the function is DATEVALUE({Date}), but when using TODAY() we need to add a FORMAT_DATE() before today to make it work.

Thank you all for your help, and hope this helps others as well! Cheers.