What is the best practice for a field that will sometimes be a date and sometimes be a date and time?

Hi Folks!

Would greatly appreciate best practice guidance for the date/time type in AppSheet…

Scenario: An announcement is scheduled for April 2021. Initially the dates are for April 1 - April 30th. As the announcement approaches, we will know the specific date/time.

I feel like this has to be done with 8 different fields but wanted folks feedback to see if there is a more simple way.

Kindly note we need the dates and times for the calendar and for a massive concatenation formula in the background building out a summary statement for an email workflow.

Proposed Fields:

  • Has End Time? [Yes/No]
  • Activity date and time [DateTime]
    • Show if [Has End Time?]=Yes
  • start date [Date]
    • Show if [Has End Time?]=No
  • end date [Date]
    • Show if [Has End Time?]=No
  • hidden start date [Date]
    • IF([Has End Time?]=“Yes”,DATE([Activity date and time]),DATE([start date]))
  • hidden start time [Time]
    • IF([Has End Time?]=“Yes”,TIME([Activity date and time]),"")
  • hidden end date [Date]
    • IF([Has End Time?]=“Yes”,DATE([Activity date and time]),DATE([end date]))
  • hidden end time [Time]
    • IF([Has End Time?]=“Yes”,TIME([Activity date and time]),"")

The Calendar UX is based on the hidden fields.

Is this the best way? Is there an easier way?

1 Like

I don’t completely understand Scenario, why Start and End Dates would have Times at all nor how Activity DateTime fits into the picture. Could you elaborate a bit more?

Also, what does it mean that “Calendar UX is based on the hidden fields”??

1 Like

How about just 4 fields; start date, start time, end date, end time?

2 Likes

That could work. I still wonder about Activity Date.

If I understand correctly, the idea is to hide the Time component until there is a Time to enter. So how do you tell the app to finally show the Time? I suppose you could have Yes/No column shown only the form that allows the person editing to signal they are ready to enter the Times.

Personally, I don’t like to separate Dates and Times that are meant to be together. For this case I would use a DateTime and set the Time component to 00:00:00. Then simply update the time when ready.

I would then show the Start Date and End Date normally along with a message: “Event Times coming soon!”. I think it’s easier implementation and more valuable to the end user.

1 Like

Have you consider simply having a Start DateTime field and an End DateTime field and then defining a “special time value” that means the time is not defined? You might pick a special time value like “00:00:00” or “23:59:59” assuming those are not legal times for an announcement. You could then custom format DataTime values and omit the time if the “special time value” is present.

3 Likes

Let’s take this a few steps back:

My teammate is managing an activity (Unicorn Launch 2021). Today, they know it will be in Q2, but not sure when. When they add this activity to the tracker we need to see it on the calendar for all of Q2 2021.

In February they will have the specific date and time for launch. They need to update their activity so we can see it on the calendar (and include which hour it will launch).

Few items of note:

  • Appsheet UX can only take one column for: Start Date, Start Time, End Date, End Time
  • Folks are a bit grumpy at the length of the form so trying to cut down on the number of fields they need to engage with.
  • My team is extra special and if they see a time option they will try to fill it in even if they don’t know it (which is why i think i need to separate out date/time from start and end date).
  • I have a complex nested if statement to concatenate a bunch of fields and output them into an email workflow to leadership. (they prefer the blurbs to a table)
    • Unicorns Launch 2021 (managed by XX) will launch between XX and XX. The executive sponsor is XX.

All of the above is leading me to think I need at least three fields for time and four hidden fields for the calendar output and email workflow statement. Check out the diagram below. Can this be done better? Maybe…. Is there a way to hide the time part of a formula based on another field?