Single Dates vs. Ranges

1

I have a calendar view for various event types in a table. Some of these events are single day events, others are multi-day events spanning ranges.

Based on what I have seen, if I want to keep these in the same calendar I need to have them in the same table. So I have been playing with the best way to show this data in the calendar without making it confusing or cumbersome for users. I am open to any suggestions about this, but below I have shared a specific solution I am trying to make work.

I tried having 3 different date fields. One for single day events, the other for ranges, but I could not get the calendar to accept this method.

Now I have two columns. “Start Date” and “End Date” - for single day events these would be the same date. The problem with this is, if a user leaves off either start or end date, the event will not appear on the calendar. So I have to “force” this without depending on users to be consistent.

My idea for forcing it is to have a toggle to “lock” start and end dates together for single day events.

So theoretically if the switch is NOT toggled they can manually edit both start and end date fields, but if the switch IS toggled, the “start date” would always be the end date (meaning they only have to edit 1 date field)

The issue I have had with making that work is that I can only have a formula OR manual input.

I am open to alternative solutions to achieve the same goal (Display both single day AND ranges on the calendar without expecting users to update the date in 2 fields for single day events)

Here is the bare minimum version of the issue: https://coda.io/d/Testing-Playground_dyoE_FrifhU/Dates-and-Ranges_sujMj#_luuro

2

Hey Sam!

I think I might have a solution for you to check out. It uses a “helper” column that can be hidden from view of your users. Take a look and let me know if this works for you.

1 Like
3

@Nicole_Macdonald that wasn’t quite it, but “switchif” and your sample got me exactly where I needed so THANK YOU so much!

I needed the start date to be the one automatically updated when the “lock dates” toggle was on - so I switched the hidden field to be “actual start date”. Then if no start date is given, it should default to the end date.

Now that I understand the switchif formula works like “if/else” I can explain my desired outcome in that way.

If the “lock dates” toggle is true, the “actual start date” should be the “end date” (not relevant if user added a start date)
If the “lock dates” toggle is false and start date is blank, the “actual start date” should be the end date.
if the “lock dates” toggle is false and start date is not blank, the “actual start date” should be the start date.

Spelling it out like that helps me to see there is only one situation in which the “actual start date” should be the “start date” so I can use that as my condition and leave it as else “actual start date” should be the “end date” leaving me with a simplified formula to handle each use case.

Here is the final formula that is working perfectly for me:

SwitchIf(
 (thisRow.[Start Date].IsNotBlank() AND thisRow.[Lock Dates]=false()), thisRow.[Start Date], 
  thisRow.[Due Date]
)
5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.