One of my colleagues had an interesting requirement (and I’m pretty sure many users might’ve), to show halfhourly timestamps in a chart, so they can show number of calls received to report issues every half hour (in a day) vs how many of them were resolved.

Updated (6/15/2016 8:44 AM EST): As few people suggested and/or requested, I’m updating this blog with another way to do this. Thanks to Amit Kumar, for the formula he suggested.

There are 2 ways of doing this, which means, 2 different formulas can be used to create the Halfhourly buckets.

  1. Get the timestamp using the formula =Formatdate([Date timestamp];”HH:mm:ss”) and then, create the 48 different halfhourly timestamp buckets.
  2. Get the timestamp using the same formula, convert each timestamp (every second) of a day to a numeric value and then, create 48 different halfhourly timestamp buckets.

Both solutions will work perfect.

Below is the data that we’ll be using during this exercise:

Date Timestamp Calls Received Calls Resolved
6/12/2016 12:00:00 AM 9 8
6/12/2016 12:15:00 AM 2 1
6/12/2016 12:30:00 AM 3 2
6/12/2016 12:45:00 AM 3 2
6/12/2016 1:00:00 AM 4 3
6/12/2016 1:15:00 AM 5 4
6/12/2016 1:30:00 AM 7 6
6/12/2016 1:45:00 AM 3 2
6/12/2016 2:00:00 AM 8 7
6/12/2016 2:15:00 AM 4 3
6/12/2016 2:30:00 AM 7 6
6/12/2016 2:45:00 AM 7 6
6/12/2016 3:00:00 AM 3 2
6/12/2016 3:15:00 AM 6 5
6/12/2016 3:30:00 AM 6 5
6/12/2016 3:45:00 AM 7 5
6/12/2016 4:00:00 AM 3 1
6/12/2016 4:15:00 AM 2 0
6/12/2016 4:30:00 AM 4 2
6/12/2016 4:45:00 AM 8 6
6/12/2016 5:00:00 AM 4 2
6/12/2016 5:15:00 AM 8 6
6/12/2016 5:30:00 AM 4 2
6/12/2016 5:45:00 AM 7 5
6/12/2016 6:00:00 AM 8 6
6/12/2016 6:15:00 AM 4 1
6/12/2016 6:30:00 AM 5 2
6/12/2016 6:45:00 AM 4 1
6/12/2016 7:00:00 AM 4 1
6/12/2016 7:15:00 AM 8 5
6/12/2016 7:30:00 AM 4 0
6/12/2016 7:45:00 AM 7 3
6/12/2016 8:00:00 AM 5 1
6/12/2016 8:15:00 AM 5 1
6/12/2016 8:30:00 AM 6 2
6/12/2016 8:45:00 AM 8 4
6/12/2016 9:00:00 AM 4 0
6/12/2016 9:15:00 AM 8 4
6/12/2016 9:30:00 AM 4 0
6/12/2016 9:45:00 AM 8 4
6/12/2016 10:00:00 AM 4 3
6/12/2016 10:15:00 AM 7 6
6/12/2016 10:30:00 AM 4 3
6/12/2016 10:45:00 AM 4 3
6/12/2016 11:00:00 AM 5 4
6/12/2016 11:15:00 AM 8 7
6/12/2016 11:30:00 AM 3 2
6/12/2016 11:45:00 AM 3 2
6/12/2016 12:00:00 PM 6 5
6/12/2016 12:15:00 PM 4 3
6/12/2016 12:30:00 PM 7 6
6/12/2016 12:45:00 PM 5 1
6/12/2016 1:00:00 PM 7 3
6/12/2016 1:15:00 PM 7 3
6/12/2016 1:30:00 PM 7 3
6/12/2016 1:45:00 PM 6 2
6/12/2016 2:00:00 PM 3 2
6/12/2016 2:15:00 PM 7 6
6/12/2016 2:30:00 PM 3 2
6/12/2016 2:45:00 PM 6 5
6/12/2016 3:00:00 PM 3 2
6/12/2016 3:15:00 PM 7 6
6/12/2016 3:30:00 PM 3 2
6/12/2016 3:45:00 PM 7 6
6/12/2016 4:00:00 PM 3 1
6/12/2016 4:15:00 PM 7 5
6/12/2016 4:30:00 PM 3 1
6/12/2016 4:45:00 PM 7 5
6/12/2016 5:00:00 PM 6 4
6/12/2016 5:15:00 PM 6 4
6/12/2016 5:30:00 PM 2 0
6/12/2016 5:45:00 PM 7 5
6/12/2016 6:00:00 PM 6 4
6/12/2016 6:15:00 PM 4 2
6/12/2016 6:30:00 PM 7 5
6/12/2016 6:45:00 PM 5 3
6/12/2016 7:00:00 PM 7 2
6/12/2016 7:15:00 PM 7 2
6/12/2016 7:30:00 PM 3 2
6/12/2016 7:45:00 PM 6 1
6/12/2016 8:00:00 PM 3 2
6/12/2016 8:15:00 PM 7 2
6/12/2016 8:30:00 PM 3 3
6/12/2016 8:45:00 PM 6 1
6/12/2016 9:00:00 PM 3 3
6/12/2016 9:15:00 PM 7 2
6/12/2016 9:30:00 PM 3 2
6/12/2016 9:45:00 PM 7 2
6/12/2016 10:00:00 PM 3 2
6/12/2016 10:15:00 PM 7 6
6/12/2016 10:30:00 PM 3 2
6/12/2016 10:45:00 PM 7 6
6/12/2016 11:00:00 PM 6 4
6/12/2016 11:15:00 PM 6 5
6/12/2016 11:30:00 PM 2 0
6/12/2016 11:45:00 PM 6 4
6/12/2016 11:59:59 PM 9 2

 

Solution 1:

Step 1:

Create the below Dimension variables

  • v_TimeStamp =FormatDate([Date Timestamp];”HH:mm:ss”) (to convert the Date Timestamp to HH:mm:ss format (24 hr format))

Updated (6/30/2016 3:57 PM EST): We can use a simple one line formula to replace the huge one with 40 If conditions. (Courtesy of Clemens Potter)

  • v_Halfhourly Buckets (this creates 48 different buckets depending on the v_TimeStamp)

=FormatDate([Date Timestamp];”HH:”) + (If(FormatDate([Date Timestamp];”mm”) < “30”) Then “00:00” Else “30:00”)

(I’ve deleted the lengthy formula, which appeared here, earlier.)

Step 2:

  1. Create a Column Chart, with v_Halfhourly Buckets on Category axis (X axis), Calls Received and Calls Resolved on Value Axis 1.
  2. Go to Format Chart -> Value Axis -> in Stacking, select Unstacked
  3. Go to Format Chart -> Plot Area -> Design -> update Spacing within Groups as 0.1 and Spacing between Groups to 0.5, that’ll make the bars for Calls Received and Calls Resolved for a Halfhourly Bucket look close to each other, which looks nice to compare the 2 values. Apply and OK.
  4. In addition to that, Format Data Series of Calls Received as Red color and Calls Resolved as Green color.

Solution 2:
Step 1:

Create the below Dimension variables

  • v_Date TimeStamp =ToDate(FormatDate([Date Timestamp];”M/d/yyyy HH:mm:ss”);”M/d/yyyy HH:mm:ss”) (to convert the Date Timestamp to 24 hr format) 
  • v_Hours =ToNumber(FormatDate([DateTimeStamp];”HH”)) (to get the Hour in 24 hr format in numeric datatype, we want it to be 24 hr format, so we don’t have to worry about AM or PM)
  • v_Minutes =ToNumber(FormatDate([DateTimeStamp];”mm”)) (to get the mins in numeric datatype)
  • v_Seconds =ToNumber(FormatDate([DateTimeStamp];”ss”)) (to get the seconds datatype)
  • v_Numeric Value =([v_Hours]*3600)+([v_Minutes]*60)+[v_Seconds] (this creates a numeric value for every sec of the day (0 – 86399), as a day has 86400 secs, logic is simple, multiple Hours by 3600 as an hour has 3600 secs, multiple Minutes by 60 as a minute has 60 secs and add the Seconds)

Now, main variable of this logic

  • v_Halfhourly Timestamps (this creates 48 different buckets depending on the v_Numeric Value)

=If([v_Numeric Value]  Between  (0;1799))  Then  “00:00:00”

ElseIf([v_Numeric Value]  Between  (1800;3599))  Then  “00:30:00”

ElseIf([v_Numeric Value]  Between  (3600;5399))  Then  “01:00:00”

ElseIf([v_Numeric Value]  Between  (5400;7199))  Then  “01:30:00”

ElseIf([v_Numeric Value]  Between  (7200;8999))  Then  “02:00:00”

ElseIf([v_Numeric Value]  Between  (9000;10799))  Then  “02:30:00”

ElseIf([v_Numeric Value]  Between  (10800;12599))  Then  “03:00:00”

ElseIf([v_Numeric Value]  Between  (12600;14399))  Then  “03:30:00”

ElseIf([v_Numeric Value]  Between  (14400;16199))  Then  “04:00:00”

ElseIf([v_Numeric Value]  Between  (16200;17999))  Then  “04:30:00”

ElseIf([v_Numeric Value]  Between  (18000;19799))  Then  “05:00:00”

ElseIf([v_Numeric Value]  Between  (19800;21599))  Then  “05:30:00”

ElseIf([v_Numeric Value]  Between  (21600;23399))  Then  “06:00:00”

ElseIf([v_Numeric Value]  Between  (23400;25199))  Then  “06:30:00”

ElseIf([v_Numeric Value]  Between  (25200;26999))  Then  “07:00:00”

ElseIf([v_Numeric Value]  Between  (27000;28799))  Then  “07:30:00”

ElseIf([v_Numeric Value]  Between  (28800;30599))  Then  “08:00:00”

ElseIf([v_Numeric Value]  Between  (30600;32399))  Then  “08:30:00”

ElseIf([v_Numeric Value]  Between  (32400;34199))  Then  “09:00:00”

ElseIf([v_Numeric Value]  Between  (34200;35999))  Then  “09:30:00”

ElseIf([v_Numeric Value]  Between  (36000;37799))  Then  “10:00:00”

ElseIf([v_Numeric Value]  Between  (37800;39599))  Then  “10:30:00”

ElseIf([v_Numeric Value]  Between  (39600;41399))  Then  “11:00:00”

ElseIf([v_Numeric Value]  Between  (41400;43199))  Then  “11:30:00”

ElseIf([v_Numeric Value]  Between  (43200;44999))  Then  “12:00:00”

ElseIf([v_Numeric Value]  Between  (45000;46799))  Then  “12:30:00”

ElseIf([v_Numeric Value]  Between  (46800;48599))  Then  “13:00:00”

ElseIf([v_Numeric Value]  Between  (48600;50399))  Then  “13:30:00”

ElseIf([v_Numeric Value]  Between  (50400;52199))  Then  “14:00:00”

ElseIf([v_Numeric Value]  Between  (52200;53999))  Then  “14:30:00”

ElseIf([v_Numeric Value]  Between  (54000;55799))  Then  “15:00:00”

ElseIf([v_Numeric Value]  Between  (55800;57599))  Then  “15:30:00”

ElseIf([v_Numeric Value]  Between  (57600;59399))  Then  “16:00:00”

ElseIf([v_Numeric Value]  Between  (59400;61199))  Then  “16:30:00”

ElseIf([v_Numeric Value]  Between  (61200;62999))  Then  “17:00:00”

ElseIf([v_Numeric Value]  Between  (63000;64799))  Then  “17:30:00”

ElseIf([v_Numeric Value]  Between  (64800;66599))  Then  “18:00:00”

ElseIf([v_Numeric Value]  Between  (66600;68399))  Then  “18:30:00”

ElseIf([v_Numeric Value]  Between  (68400;70199))  Then  “19:00:00”

ElseIf([v_Numeric Value]  Between  (70200;71999))  Then  “19:30:00”

ElseIf([v_Numeric Value]  Between  (72000;73799))  Then  “20:00:00”

ElseIf([v_Numeric Value]  Between  (73800;75599))  Then  “20:30:00”

ElseIf([v_Numeric Value]  Between  (75600;77399))  Then  “21:00:00”

ElseIf([v_Numeric Value]  Between  (77400;79199))  Then  “21:30:00”

ElseIf([v_Numeric Value]  Between  (79200;80999))  Then  “22:00:00”

ElseIf([v_Numeric Value]  Between  (81000;82799))  Then  “22:30:00”

ElseIf([v_Numeric Value]  Between  (82800;84599))  Then  “23:00:00”

ElseIf([v_Numeric Value]  Between  (84600;86399))  Then  “23:30:00”

Step 2:

  1. Create a Column Chart, with v_Halfhourly Timestamps on Category axis (X axis), Calls Received and Calls Resolved on Value Axis 1.
  2. Go to Format Chart -> Value Axis -> in Stacking, select Unstacked
  3. Go to Format Chart -> Plot Area -> Design -> update Spacing within Groups as 0.1 and Spacing between Groups to 0.5, that’ll make the bars for Calls Received and Calls Resolved for a Halfhourly Timestamp look close to each other, which looks nice to compare the 2 values. Apply and OK.
  4. In addition to that, Format Data Series of Calls Received as Red color and Calls Resolved as Green color.

Voila, we did it. This is what the chart looks like, now.

Snap 01.png

Please try it and let me know your feedback, so I can fix if there are any issues or improve it further.
Thanks,
Mahboob Mohammed

To report this post you need to login first.

21 Comments

You must be Logged on to comment or reply to a post.

  1. Amit Kumar

    have you tried with this ?

    =If(FormatDate([Date Timestamp];”HH:mm:ss”)) Between (“00:00:00″;”00:30:00”) Then “00:30:00″  ElseIf(FormatDate([Date Timestamp];”HH:mm:ss”)) Between (“01:00:00″;”01:30:00”) Then “01:30:00” Elseif ….

    (0) 
    1. Mahboob Mohammed Post author

      Hi Amit,

      I tried just the FormatDate([Date TimeStamp];”HH:mm”) and then did it differently as I wanted to do it in a different way. But, I’ll think about it and may be edit the blog post.

      Thanks for your comments,

      Mahboob Mohammed

      (0) 
  2. Ray Khan

    This will come handy Mahboob. I think Amit has a great point, may be you should add a 2nd way of doing this with a different formula (the formula that Amit has).

    Ray Khan

    (0) 
    1. Mahboob Mohammed Post author

      Thanks for stopping by and the suggestion Ray Khan!!!

      That update is coming soon, I’ll update the blog soon to have 2 different ways of doing it, basically using 2 different formulas.

      Mahboob Mohammed

      (0) 
    1. Mahboob Mohammed Post author

      Thanks for the compliment and suggestion KD!!!

      That update is coming soon, I’ll update the blog soon to have 2 different ways of doing it, basically using 2 different formulas.

      Mahboob Mohammed

      (0) 
  3. Clemens Pötter

    Hi Mahboob,

    In order to avoid many “ElseIf”s, can you try to concatenate the hours with “00:00” or “30:00” like this:

    =FormatDate([Date Timestamp];”HH:”) + (If(FormatDate([Date Timestamp];”mm”) < “30”) Then “00:00” Else “30:00”)

    Best Regards

    Clemens

    (1) 
  4. eugene lewis

    Hi ,

    Brilliant report, was able to follow instruction and compile the graph.

    Now my boss was so impress , he require additional information too also display open/backlog ticket.

    I tried a couple of ways and had no luck.

    Have anybody else had the same requirements and were able to resolve?

    (0) 
  5. eugene lewis

    Hi,

    As an additional line graph with open and closed tickets as bars.

    I tried formula: (calls received + previous open tickets)- calls resolved = open tickets

    Date Timestamp Calls Received Calls Resolved Open tickets
    6/12/2016 12:00:00 AM 9 8 1
    6/12/2016 12:15:00 AM 2 1 2
    6/12/2016 12:30:00 AM 3 2 3
    (0) 
    1. Mahboob Mohammed Post author

      Hi Eugene,

      So, what happened? Were you not able to get it? Or the Open Tickets calculation is showing you wrong numbers? I don’t understand what is your question here.

      Thanks,
      Mahboob Mohammed

      (0) 
  6. eugene lewis

    Hi,

    As per excel below- I am unable to get the open tickets calculation correct.

    Formula: Open Bal + Inc Submit – Inc closed = Open Tickets
    Balance of open tickets brought forward to next month as Open Bal. 

    (0) 

Leave a Reply