Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 23, 2020 04:14 PM
Hi all. New to Airtable. I help out with a volunteer organization, and volunteers use the Airtable base to keep track of incoming calls. I would like to find out at what times we receive the most calls. I’ve tried playing around with blocks and I can find out the most popular days of the week, but is there a way to get more granular? (for example, a bar graph that shows how many records were created between 9am-12pm, 12pm-3pm, 3pm-7pm)?
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'
)
)
)
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.
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'
)
)
)
)
)
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.
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?
Aug 07, 2020 10:40 AM
I actually figured it out using the formula DATETIME_FORMAT({Date},'dddd')
and filters. Thank you so much for all your help!