Calculated field for two custom date fields?

[HeatherLa2]

On a custom form, we have two custom date fields; one for an Event and one for a Digital Event. The digital event includes the time of day, while the event date does not (which is why we need two separate fields). I would like to create a calculated date field bringing in those two dates ("Calendar Date") so I can use the custom date feature on a calendar report. The team wants to see both Events and Digital Events together in one filter option.

I thought I could use the "or" expression of || between the two fields, but it doesn't look like that works.

Can anyone think of another way I should approach this so that I can get to one calculated date field?

[AnthonyIm]

Hi - I'm a little confused. Not sure why you want two different dates to be "the same" then in a different field. Since they are custom fields, what stops someone from putting Digital Event Date and Start Time as 8/6/2020 at 12:00pm and Event Start Date as 8/7/2020?

Also, I'm not sure you really need two separate fields. In an report or view, you can change a date column to not show the time. So if for a status report you only care about 8/6/2020 and in another Event Details report you want 8/6/2020 at 12:00pm, you can do that with the same field.

As for the calendar, my recommendation is to add them both to the calendar and if they want them the same color (I'm assuming that is what you mean by "one filter"), just make them the same color.

Let me know if any of that is making sense or if I just complicated it. LOL!

[HeatherLa2]

Thanks for responding, Anthony!

A little more context... we have a company-wide Event Calendar request queue. A user submits a request that essentially gets approved and then goes to the calendar, where everyone can see it.

The first question is whether it is a digital or a in-person event. If it is a digital event, the date field has the time of day option for the start and end dates. The in-person events don't need the start and end time there. So that's all fine and good when I create my reports... I group them by event type and we go on our way.

The problem is when I go into creating the actual calendar report. Instead of being able to show everything in each of the regions (AMER, EMEA, APAC) I have to separate them out into Digital and In-Person because of the two different date fields. Through testing, we've found that our users don't want all of the options... they just want the regions. So I'd like the calculated field to take both the digital and in-person date fields, so that "Calendar Date" would be what I put into Custom Start Date and End Date fields. I hope this makes more sense.

[skyehansen]

Would the calculated field just be a concat of the two dates, formatted as a date? Something like CONCAT(date1,date2)? Sounds like they are both not used at the same time, correct?

[AnthonyIm]

Okay!! So it is an either/or scenario! I would recommend an IF statement for your calculation. So something like IF(ISBLANK(Event Start Date,Digital Event Date and Start Time,Event Start Date)

[HeatherLa2]

Hi, @Anthony Imgrund‚ and @Skye Hansen‚ - I really appreciate both of you stepping in to help! The CONCAT calculation worked exactly as I needed it to and now I have the calendar up and running just the way our users want to see it.

Anthony, I didn't try your calculation, only because the CONCAT that Skye recommended worked. Thank you so much for responding! :)

Have a great weekend!