Value function returning Error

[WilliamPorter]

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?

[Kamille_Parks]

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

[WilliamPorter]

Yes, CreatedDate and DueDate are both Date fields.

[ScottWorld · Accepted solution]

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.

[WilliamPorter]

ScottWorld:

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.

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.

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

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.

[ScottWorld]

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

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

[kuovonne]

WilliamPorterTech:

Dates in, dates out, right? Nope.

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)

[WilliamPorter]

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.