Skip to main content
Solved

Help with formula writing for a duration

  • June 5, 2020
  • 6 replies
  • 39 views
L
Forum|alt.badge.img+8

nOt sure if I’ll ever get the hang of this formula thing.

How do I write a formula to calculate the minutes based on hours times days using these types of options the columns are and both field types are a single select resulting in these kind of combinations?

|4 day workshop| |Full Day 8 hours 8-5|
|4 day workshop| |Full Day 6 hours 9-4|
|3 day Workshop| |Full Day 8 hours 8-5|
|3 day Workshop| |Full Day 6 hours 9-4|
|2 day workshop| |Full Day 8 hours 8-5|
|2 day workshop| |Full Day 6 hours 9-4|
|1 day Workshop| |Full Day 8 hours 8-5|
|1 day Workshop| |Full Day 6 hours 9-4|

Best answer by Jeremy_Oglesby

Hi @Leslie_Miller – don’t be too hard on yourself, because this one isn’t necessarily an easy formula. Since you are wanting to process numbers that are located in the text of Single-select Options, it’s not exactly straight-forward.

Since it looks like the number of options you are dealing with is fairly manageable (I see 4 options for day counts, and 2 options for hour counts), I’d suggest using a SWITCH() function as the main logical element for deriving the values to operate on, and just directly mapping each possible option to an integer value. Something like this should work:

SWITCH(
   {<<name of your field with workshop days>>},
   "4 day Workshop", 4,
   "3 day Workshop", 3,
   "2 day Workshop", 2,
   "1 day Workshop", 1
) *
SWITCH(
   {<<name of your field with workshop hours>>},
   "Full Day 8 hours 8-5", 480,
   "Full Day 6 hours 9-4", 360
)

Based on the option selected for days, we produce the number of days to use in the calculation as an integer.

Based on the option selected for daily hours, we produce the number of minutes to use in the calculation as an integer.

The values returned by each of the SWITCH() statements are then multiplied together.

6 replies

J
Forum|alt.badge.img+18

Hi @Leslie_Miller – don’t be too hard on yourself, because this one isn’t necessarily an easy formula. Since you are wanting to process numbers that are located in the text of Single-select Options, it’s not exactly straight-forward.

Since it looks like the number of options you are dealing with is fairly manageable (I see 4 options for day counts, and 2 options for hour counts), I’d suggest using a SWITCH() function as the main logical element for deriving the values to operate on, and just directly mapping each possible option to an integer value. Something like this should work:

SWITCH(
   {<<name of your field with workshop days>>},
   "4 day Workshop", 4,
   "3 day Workshop", 3,
   "2 day Workshop", 2,
   "1 day Workshop", 1
) *
SWITCH(
   {<<name of your field with workshop hours>>},
   "Full Day 8 hours 8-5", 480,
   "Full Day 6 hours 9-4", 360
)

Based on the option selected for days, we produce the number of days to use in the calculation as an integer.

Based on the option selected for daily hours, we produce the number of minutes to use in the calculation as an integer.

The values returned by each of the SWITCH() statements are then multiplied together.

L
Forum|alt.badge.img+8
  • Leslie_Miller
  • Author
  • Known Participant
  • June 5, 2020

YOU ARE BRILLIANT THANK YOU!

J
J
Forum|alt.badge.img+18

YOU ARE BRILLIANT THANK YOU!


:slightly_smiling_face: If it’s not too much trouble, @Leslie_Miller – would you mind marking my response as the solution, rather than yours?

Thank you

Justin_Barrett
Justin_Barrett
Forum|alt.badge.img+21

Just to offer another angle on things, the first part of that formula could be simplified a touch. Because each of those options begins with a number, that number could be extracted and used directly:

VALUE(LEFT({<<name of your field with workshop days>>}, 1)) *
SWITCH(
   {<<name of your field with workshop hours>>},
   "Full Day 8 hours 8-5", 480,
   "Full Day 6 hours 9-4", 360
)
J
Forum|alt.badge.img+18

Just to offer another angle on things, the first part of that formula could be simplified a touch. Because each of those options begins with a number, that number could be extracted and used directly:

VALUE(LEFT({<<name of your field with workshop days>>}, 1)) *
SWITCH(
   {<<name of your field with workshop hours>>},
   "Full Day 8 hours 8-5", 480,
   "Full Day 6 hours 9-4", 360
)

That certainly condenses the formula - I’m not sure I would say it simplifies the formula :slightly_smiling_face:

Justin_Barrett
Justin_Barrett
Forum|alt.badge.img+21

I see your point, but to me it feels a little simpler. Instead of using the field data to determine what value to output, it creates the output value directly from the field data. There’s no need to see which of the four possible options it is because the value needed is in the data itself.

Terms & ConditionsAccessibility statement