Skip to main content
Solved

Why does this not return a date value?

  • November 5, 2021
  • 7 replies
  • 60 views
WilliamPorter
Forum|alt.badge.img+19

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?

Best answer by 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.

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

7 replies

Kamille_Parks11
Forum|alt.badge.img+27

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

WilliamPorter
Forum|alt.badge.img+19
  • WilliamPorter
  • Author
  • Brainy
  • November 5, 2021

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


Yes, CreatedDate and DueDate are both Date fields.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • Answer
  • November 5, 2021

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
WilliamPorter
Forum|alt.badge.img+19
  • WilliamPorter
  • Author
  • Brainy
  • November 5, 2021

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.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • November 5, 2021

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"
)
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • November 5, 2021

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
Forum|alt.badge.img+19
  • WilliamPorter
  • Author
  • Brainy
  • November 6, 2021

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.

kuovonne
Terms & ConditionsAccessibility statement