How to find the most popular shift times

[Edith_Claudio]

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

[JonathanBowen]

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

[Edith_Claudio]

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.

[JonathanBowen]

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

[JonathanBowen]

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.

[Edith_Claudio]

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?

[Edith_Claudio]

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