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
- Other questions
- Re: How to find the most popular shift times

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

0
1624
1

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

Jul 23, 2020 04:14 PM

6 Replies 6

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

Jul 24, 2020 01:03 AM

Hi @Edith_Claudio - with a bit of formula work, you can classify each call into a “time group”, then chart on this group. For example, let’s say your calls table is like this (I’m assuming you are already capturing the date/time of the call) :

You can get the time as an integer using this formula:

`DATETIME_FORMAT({Date/Time}, 'H')`

and then group the times using something like this:

```
IF(
AND(Time >= 9, Time < 12),
'9am - 12pm',
IF(
AND(Time >= 12, Time < 15),
'12pm - 3pm',
IF(
AND(Time >= 15, Time <= 19),
'3pm - 7pm'
)
)
)
```

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

Aug 05, 2020 02:32 PM

This is SO helpful! Exactly what I needed. Thank you so much! Quick question. I don’t know much about formulas but am trying to change it so that the group times are now as follows:

9am-11am

11am-1pm

1pm-3pm

3pm-5pm

5pm-7pm

Would you mind writing out the formula again for the above? I have tried so many times following the pattern but I keep getting an error.

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

Aug 06, 2020 03:09 AM

Hi - yes, nested IFs can be a bit tricky. Try this:

```
IF(
AND(Time >= 9, Time < 11),
'9am - 11am',
IF(
AND(Time >= 11, Time < 13),
'11am - 1pm',
IF(
AND(Time >= 13, Time < 15),
'1pm - 3pm',
IF(
AND(Time >= 15, Time < 17),
'3pm - 5pm',
IF(
AND(Time >= 17, Time < 19),
'5pm - 7pm',
'Other'
)
)
)
)
)
```

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

Aug 06, 2020 03:13 AM

The trick is to make the first IF statement, then copy this into the last part of itself for as many times as you need to. So, I start with this:

```
IF(
A = 1,
'Answer 1',
XXXXXX
)
```

I then copy the whole IF statement and replace the XXXXXX with my copy - so this:

```
IF(
A = 1,
'Answer 1',
IF(
A = 1,
'Answer 1',
XXXXXX
)
)
```

Then edit the second IF as needed:

```
IF(
A = 1,
'Answer 1',
IF(
A = 2,
'Answer 2',
XXXXXX
)
)
```

and repeat.

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

Aug 06, 2020 07:50 AM

Thank you SO MUCH Jonathan! I am learning a lot and this is going to be extremely helpful to my group.

I have (hopefully) one more question. Now being able to visualize the data in blocks, I’d like to know if there is a way that I can split between weekdays and weekends?

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

Aug 07, 2020 10:40 AM

`DATETIME_FORMAT({Date},'dddd')`

and filters. Thank you so much for all your help!

Reply