Skip to main content

This ought to be easy. I want to display records on a calendar by a single date field, but the date field for display is calculated: It’s the DueDate if there is one entered, otherwise it’s CreatedDate.

IF( DueDate=BLANK(), CreatedDate, DueDate )

Dates in, dates out, right? Nope. A formula field using the above formula cannot be used in the calendar, apparently because the result of the formula is not recognized as a date.

Now the following DOES work:

DATETIME_PARSE(
	DATETIME_FORMAT(
		IF(DueDate=BLANK(),CreatedDate,DueDate),"MM/DD/YYYY"
		),"MM/DD/YYYY"
)

Seriously? Is there not an easier way to do this?

What do both {CreatedDate} and {DueDate} look like? Are they both truly dates?

What do both {CreatedDate} and {DueDate} look like? Are they both truly dates?


Yes, CreatedDate and DueDate are both Date fields.

Your original formula works just fine for me, and it also works on the calendar view as well. I also tested your formula with a synced field too, just like your screenshot.

p.s. Your subsequent formula has unnecessary redundancies in it — you don’t need both DATETIME_FORMAT and DATETIME_PARSE.

Your original formula works just fine for me, and it also works on the calendar view as well. I also tested your formula with a synced field too, just like your screenshot.

p.s. Your subsequent formula has unnecessary redundancies in it — you don’t need both DATETIME_FORMAT and DATETIME_PARSE.


THANK YOU for testing, Scott, and for responding. I have no idea what was wrong earlier but it was driving me crazy. I just rebuilt the calc as a new field and confirmed that the new field does work. Happy with that result.

You know, I tried that several ways and couldn’t get a date result. Thanks for pointing that out; I’ll play with it later just to understand it better.

THANK YOU for testing, Scott, and for responding. I have no idea what was wrong earlier but it was driving me crazy. I just rebuilt the calc as a new field and confirmed that the new field does work. Happy with that result.

You know, I tried that several ways and couldn’t get a date result. Thanks for pointing that out; I’ll play with it later just to understand it better.


You’re welcome! Your 2nd formula should work just fine like this:

DATETIME_FORMAT(
IF(DueDate=BLANK(),CreatedDate,DueDate),
"MM/DD/YYYY"
)

I find this situation curious. Did the grid view show the original formula formatted as a date? Did the original formula have date/time formatting options? If so, I am surprised that it could not be used in a calendar. I get a warning that the date field cannot be dragged around, but it can still be displayed.

It seems to me that a simpler formula should work:

IF(DueDate, DueDate, CreatedDate)

I find this situation curious. Did the grid view show the original formula formatted as a date? Did the original formula have date/time formatting options? If so, I am surprised that it could not be used in a calendar. I get a warning that the date field cannot be dragged around, but it can still be displayed.

It seems to me that a simpler formula should work:

IF(DueDate, DueDate, CreatedDate)

Yeah, I find it curious too. I don’t rush over here to post a question when something isn’t working: I bang my head against the wall for a while.

Unfortunately my curiosity is not going to be satisfied. I do not recall exactly what I did myself. I do recall checking the formatting options and seeing that there weren’t any. And I wrote the formula exactly as I showed it in my original post. (That’s too simple for me to misremember.) I went back to an earlier snapshot from Nov 1 — two days or so before I created that calc field. The two referenced fields (CreatedDate and DueDate) are/were both date fields.

I hate when stuff like this happens — when something gets fixed and I never figure out exactly what was wrong in the first place. But I’m grateful to have it fixed.

Terms & ConditionsAccessibility statement