Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Help with formula writing for a duration

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
2168
6

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 05, 2020 01:45 PM

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|

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 05, 2020 02:01 PM

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 6

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 05, 2020 02:01 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 05, 2020 03:10 PM

YOU ARE BRILLIANT THANK YOU!

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 05, 2020 03:14 PM

: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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 05, 2020 11:55 PM

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
)
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 06, 2020 12:35 PM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 06, 2020 02:13 PM