Help

Re: Can't use a date calculated by Formula Field in Calendar View [SOLVED]

Solved
Jump to Solution
3032 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Y_K
7 - App Architect
7 - App Architect

I calculate a member’s upcoming birthday by using formula.

IF({birthday} = “”, “”,DATETIME_FORMAT(DATEADD({birthday}, DATETIME_DIFF(TODAY(), {birthday}, ‘years’) + 1, ‘year’), “YYYY-MM-DD”))

So if someone’s birthday is 1980-05-20, the field will show 2017-05-20. I want to show this in a calendar view. However it does not allow a Formula field (only allows static Date field). Is there a way to set the calculated field to have date-time format?

1 Solution

Accepted Solutions
JB_Bakst
5 - Automation Enthusiast
5 - Automation Enthusiast

As of June 2017, you can now use formulaic dates in calendar view :slightly_smiling_face:

One thing to note is that the DATETIME_FORMAT formula returns a string, not a date. You can convert a date string into a date using the DATETIME_PARSE formula.

In the example above, the DATEADD formula already returns a date, so the complete formula would become:

IF(
  {Subscription Start} = BLANK(),
  BLANK(),
  DATEADD({Subscription Start}, 365, 'days')
)

This will return a date, which you can use in a calendar view!

See Solution in Thread

14 Replies 14
Lance_Recker
5 - Automation Enthusiast
5 - Automation Enthusiast

There doesn’t seem to be a good solution for this yet. Check out this thread for other ideas.

Stefan_Stanisla
4 - Data Explorer
4 - Data Explorer

I have a similar problem. Why can you not have a formula in a date type field?

Dave_Nykanen
5 - Automation Enthusiast
5 - Automation Enthusiast

I also have this request.

My use case involves a table of invoices and wanting a calendar view showing the date 30 days after the invoice was created, serving as a reminder to send out late payment notices.

This a million times. Subscription start & end dates are very common for many businesses, and we need a way for a formula driven date to appear within the calendar view.

For example, the below is a “Subscription End” formula column, and I need the resulting date to appear in the calendar view to server as a follow up reminder.

IF({Subscription Start} = BLANK(), BLANK(), DATETIME_FORMAT(DATEADD({Subscription Start}, 365, 'days'), "DD-MM-YYYY"))

JB_Bakst
5 - Automation Enthusiast
5 - Automation Enthusiast

As of June 2017, you can now use formulaic dates in calendar view :slightly_smiling_face:

One thing to note is that the DATETIME_FORMAT formula returns a string, not a date. You can convert a date string into a date using the DATETIME_PARSE formula.

In the example above, the DATEADD formula already returns a date, so the complete formula would become:

IF(
  {Subscription Start} = BLANK(),
  BLANK(),
  DATEADD({Subscription Start}, 365, 'days')
)

This will return a date, which you can use in a calendar view!

I was meaning to reply to this thread, suggesting that I somehow got the formulas returns working in a calendar. :slightly_smiling_face: Thanks for the confirmation JB_Bakst!

Chris_Sturgis
4 - Data Explorer
4 - Data Explorer

Thus far I have been able to get formula’s to appear as dates by using an if statement which when false results in a fixed date. It’s not perfect but it allows me to put the results on a calendar. For instance:

IF({Follow Up Period} != “”,DATEADD({Last Contact},{Follow Up Period},‘Days’), DATETIME_PARSE(‘1111-11-11’))

This equation reviews the Follow Up Period field, if it is not blank then it adds the number of days specified in that field to the Last Contact date. If the Follow Up Period field is blank then it fills the field with the date of 11/11/1111

(for some reason this formula is actually turning up results that are all 1 day short, even the else date shows up as 11/10/1111)

Instead of filling the field with the date of 11/11/1111, you may find it preferable leave these cells blank:

IF({Follow Up Period} != “”,DATEADD({Last Contact},{Follow Up Period},‘Days’),BLANK())

(for some reason this formula is actually turning up results that are all 1 day short, even the else date shows up as 11/10/1111)

You may be able to solve this by changing the value of the formatting option “Use the same timezone for all collaborators”.

If you do that then you cannot format the cell as a date and cannot put it into a calendar view.

Edit: I take that back, it seemed to have worked just fine