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_Stanislawski]

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.

[Karl_Hosking1]

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]

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

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!

[Karl_Hosking1]

I was meaning to reply to this thread, suggesting that I somehow got the formulas returns working in a calendar. 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

[David_Earley]

Using the formula in the Calender is working but the Times all translate to 12:00. This is the formula I am using.
Trying to shift the time to GMT so I can export to Google Calendar and have the times right.

 IF({Taping Date Time (CT)} = "", BLANK(), DATEADD({Taping Date Time (CT)}, -6, 'hour'))

Even Setting it to a timestamp field dose not work.

{Taping Date Time (CT)}

[Solved] Just figured out I needed to change the field format to not 12 hour.

[Graphic_Ragz]

David,

Will you explain how you solved the 12:00am issue in a little more detail? I’m so close to figuring it out but I just can’t quite get there.

Edit: Never mind, I just figured it out! See, I knew I was close.

[Marissa_Blahnik]

I’m using the DATEADD formula to create a new field for date and it is populating a calendar successfully, however it includes the 12:00a timestamp.

My formula currently is: IF({Publish Date},DATEADD({Publish Date},-7,‘days’))

When I add DATETIME_FORMAT to remove the timestamp, it no longer will populate the calendar AND it removes my IF({Publish Date} and returns errors on dates with no publish date.

DATETIME_FORMAT(IF({Publish Date},DATEADD({Publish Date},-7,‘days’)), ‘MM/DD/YYYY’)

Can anyone tell me what I am doing wrong?

Thank you!