Reports Date Format

  • 4
  • Idea
  • Updated 4 years ago
Now that the Command Centre is on general release, this may be less of an issue because those reports seem to be in a more interpretable format. For those still using the 'traditional' admin reports, I wanted to share a work around specifically with the date fields which BlueJeans push out in US date format (MM/DD/YYYY).  When I try and load these into my Spreadsheet with British regional settings (DD/MM/YYYY),  Excel gets very confused about how to interpret the dates.

A quick easy fix is to create a new column, and use this formula to dump the data out into an expected format.  
=IF(ISNUMBER(A2),DATE(TEXT(A2,"yyyy"),TEXT(A2,"dd"),TEXT(A2,"mm")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))
I apply these to my regular usage reports and allows me to pivot the table data into dashboard tables and charts.
Photo of Nick Verdegem

Nick Verdegem, Champion

  • 994 Points 500 badge 2x thumb

Posted 4 years ago

  • 4
Photo of Community Team

Community Team, Community Admin

  • 5,652 Points 5k badge 2x thumb
@Nick - That is a great point and workaround. Really appreciate the time you took to share with the other Command Centre users. I am sharing this post with our UK Customer Success, Sales and head of Command Center Product Manager. Keep up the good tips Nick!
Photo of Nina

Nina

  • 154 Points 100 badge 2x thumb
This is great, thank you Nick!