Hi there! Still getting used to Monday, but I’m trying to use the formula column to return the earliest date from multiple timeline columns. I’ve tried minimum and >/< functions but some reason it’s not working. Likely something to do with my syntax but would appreciate help. I’ve checked a couple of seemingly similar posts but nothing seems to work.
Hi @CynthiaD,
As far as I know MIN only works with numbers. So you would need to use an If Statement
IF({Timeline1#Start}>{Timeline2#Start},{Timeline1#Start},{Timeline2#Start})
If you want to format it “YYYYMMDD”, then
IF({Timeline1#Start}>{Timeline2#Start},FORMAT_DATE({Timeline1#Start},"YYYYMMDD"),FORMAT_DATE({Timeline2#Start},"YYYYMMDD"))
Thanks @GCavin - let me try that. I thought as much with MIN function, the only problem is that I want to compare up to 5 date columns. I can write a very long if statement if thats the only way but I wanted to make sure first. Thanks again
Your other option is to convert the dates into a number, then to use MIN.
MIN(DATEVALUE(FORMAT_DATE({Timeline#Start})),DATEVALUE(FORMAT_DATE({Timeline1#Start}))
This is simpler if you have 5 dates.
You’ll get the first date formatted as a number. To get it back to a date you’ll need to use this syntax:
FORMAT_DATE(ADD_DAYS("1899-12-31",MIN(DATEVALUE(FORMAT_DATE({Timeline#Start})),DATEVALUE(FORMAT_DATE({Timeline1#Start})))-1.0001),"YYYYMMDD")
Refer to The monday Hidden Functions – The Monday Man for more details.
2 Likes
Thanks so much @GCavin !! Will try that today. Really appreciate it
Thanks so much again @GCavin ! I was able to get the right minimum by just adding “FORMAT_DATE” to your initial MIN function. Is there a reason you suggest using the ADD_DAYS("1899-… portion?
So essentially:
FORMAT_DATE(MIN(DATEVALUE(FORMAT_DATE({Timeline#Start})),DATEVALUE(FORMAT_DATE({Timeline1#Start})))
I get back the date I want but want to make sure there wasn’t a special Expert reason you were adding the ADD_DAYS function?
Thanks so much again!!
I get back the date I want but want to make sure there wasn’t a special Expert reason you were adding the ADD_DAYS function?
The explanation is at https://themondayman.com/hidden/?#date-and-time-functions in the blue frame.
2 Likes
Ahn! Thank you @GCavin. So essentially, if I use the shorter formula, I could run into some issues down the line even if it generally works.