Date Formula for IF Blank then show "Date is Blank"

Hi, i have a formula that works great to add 24 months to the date from date column Date Consent Received. I can’t seem to get my IF formula correct so that if the Date Consent Received is empty it will display a message like No Consent Date Set.
This is my working formula:
FORMAT_DATE(IF(MONTH(DATE(YEAR({Date Consent Received}),MONTH({Date Consent Received})+24,1)) = MONTH(DATE(YEAR({Date Consent Received}),MONTH({Date Consent Received})+24,DAY({Date Consent Received}))),DATE(YEAR({Date Consent Received}),MONTH({Date Consent Received})+24,DAY({Date Consent Received})),SUBTRACT_DAYS(DATE(YEAR({Date Consent Received}),MONTH({Date Consent Received})+24+1,1),1)), “D MMM, YYYY”)

This is what i’m trying with my IF version:
IF({Date Consent Received},“No Date”,FORMAT_DATE(IF(MONTH(DATE(YEAR({Date Consent Received}),MONTH({Date Consent Received})+24,1)) = MONTH(DATE(YEAR({Date Consent Received}),MONTH({Date Consent Received})+24,DAY({Date Consent Received}))),DATE(YEAR({Date Consent Received}),MONTH({Date Consent Received})+24,DAY({Date Consent Received})),SUBTRACT_DAYS(DATE(YEAR({Date Consent Received}),MONTH({Date Consent Received})+24+1,1),1)), “D MMM, YYYY”),0)

Any help greatly appreciated!

@mike.h

Your formula is quite impressive. I gave up trying to figure it out.

My version is not as impressive. But I think it will get you close to what you want:

IF({Date Consent Received}, 
   FORMAT_DATE(EDATE(FORMAT_DATE({Date Consent Received}), 24), "D MMM, YYYY"),
"No Date")

It is using an undocumented function “EDATE”. You can find out more about some of the undocumented monday functions here: The monday Hidden Functions – The Monday Man


Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Creating subitems using Make/Integromat The Monday Man (EASY) Way
Contact me directly here: Contact – The Monday Man

As always Jim your reply is spot on and worked great! I’ll take a look at EDATE as i suspect i’ll have other uses for it. Thank you :+1:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.