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

[Y_K]

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?

[Lance_Recker]

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

Calendar view of birthdays

This is a very clever solution. But, what happens when you go to add another kid/record to the database? The "New Date" field will not populate automatically since the formula has now been removed. You will need to go back and change the column to a formula format again adding the formula once more and repeat the above process it seems each time, correct? I suppose that if the roster of players is constant over a season that wouldn't necessarily be too much of an issue. But, it would be…

[Stefan_Stanisla]

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

[Dave_Nykanen]

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.

[Karlstens]

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 · Accepted solution]

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!

[Karlstens]

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]

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)

[Matt_Bush]

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”.

[Chris_Sturgis]

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