# How to find the most popular shift times

Topic Labels: Extensions
809 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

6 Replies 6
13 - Mars

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'
)
)
)``````
5 - Automation Enthusiast

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.

13 - Mars

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

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

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

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

Then edit the second IF as needed:

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

and repeat.

5 - Automation Enthusiast

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?

5 - Automation Enthusiast

I actually figured it out using the formula `DATETIME_FORMAT({Date},'dddd')` and filters. Thank you so much for all your help!