Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Why does this not return a date value?

Topic Labels: Formulas
Solved
Jump to Solution
446 7
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

1 Solution

Accepted Solutions

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.

See Solution in Thread

7 Replies 7

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

Yes, CreatedDate and DueDate are both Date fields.

image

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.

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)

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.