Populate Time field based on user selections in two other fields.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I have a Date/Time field set up that is currently set for Atlantic Time. I have a list of checkboxes for provinces that when the user selects, it inputs it into a field titled Provinces impacted. What i would like is a separate field that takes the time from the date and time field and enters the time in the timezone of the province selected. i am not sure where to start with this.

Photo of Luke

Luke

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
I can help you with the formula, but its not clear from your question if you are populating 1 field or a series of fields as you said that you are populating a list of provinces (plural) affected.

In general, the formula would be

[My Date/ Time Field] - hours(1) to get to Ontario time from Atlantic time or
[My Date/ Time Field] + hours(0.5) to get to NFLD time.
Photo of Luke

Luke

  • 0 Points
Thanks Mark.

Here is how it is setup. I have a checkbox for each province set and then have another field called "Province(s) Impacted" with this formula so that whatever is selected shows up in this field.

List(", ",
If([ON], "ON", null),
If([QC], "QC", null),
If([NS], "NS", null),
If([NB], "NB", null),
If([MB], "MB", null),
If([BC], "BC", null),
If([PE], "PE", null),
If([SK], "SK", null),
If([AB], "AB", null),
If([NL], "NL", null),
If([NT], "NT", null),
If([YT], "YT", null),
If([NU], "NU", null)
)

I also have a date/time field that will be filled out labeled "Planned Start", this is in Atlantic time.

So i was hoping to take the time from the Planned Start field and  based on what province selected in Province(s) Impacted populate the local time in a field called Local Time. It could change if they picked BC or NB where the time zones are different.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
Two comments from Toronto ON in the Eastern Time Zone.

1. you don't need all those nulls, if you are silent on the "else" condition of an IF, then it will be null automatically.

2. Is the user allowed to choose 2 Provinces?  if so, what time zone do you want to use for the planned start Local Timezone field.
Photo of Luke

Luke

  • 0 Points
Thanks, I will fix those nulls. And yes there would be times they would need to select two provinces that would have different time zones. Would it be possible to use either the first selected, or to show both in the same field such as AB 10:00AM,BC 9:00AM?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
you could try this



List(", ",
If([ON],  "ON" & ToText([Planned Start] - hours(1))),
If([QC], "QC"
& ToText([Planned Start] - hours(1))),


If([NS], "NS" & ToText([Planned Start] - hours(0))),

etc



If([NU], "NU"
& ToText([Planned Start] - hours(1)))

)

There are some messy issues, for example not all of NU is the same time zone and SASK does not observe daylight saving time, so to do that perectly you would need to know the date and have a more complicated formula.
Photo of Luke

Luke

  • 0 Points
Thanks Mark this looks great! I can figure out the timezones I believe. Do you know if it would be possible to have it list multiple times like this? ON 07-16-2015 07:00 AM,AB07-16-2015 04:00 AM
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
If([ON],  "ON "  & totext([Planned Start Date]) & " " & ToText([Planned Start] - hours(1))),
Photo of Luke

Luke

  • 0 Points
Thank you! that worked