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

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

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

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"))

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

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!

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

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.

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

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.

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!

Add BLANK() to your IF statement.
IF({Publish Date},DATEADD({Publish Date},-7,‘days’), BLANK())

Same problem, any solution to this?