When you export data from Civic Review, you may see some date columns that look a little funny. For example:

This format is called ISO 8601. The date is also reflected in the GMT timezone (called UTC). Despite this format being quite universal, Excel does not recognize it as a date.

If you come across a report that has this format of dates, you can sort alphabetically, and it works perfectly! But you can't filter by year, or re-format the date into a nicer way. So here's what you can do:

Add a few new columns, this is where the formatted data will go:

This formula will convert the ISO 8601 date into a date timestamp that Excel recognizes. The great thing is Excel has a formula that can understand this date. Here's the formula

C2 is the cell where the ISO date is found in our example. So here's where you put the formula:

After you hit "enter", you'll now see a number in this column. For this date, it's 45292. This is an Excel date, we'll format it in a minute.

You can now copy this formula to the other columns, and Excel will auto-update the referenced columns. Here's a screenshot to illustrate:

You can similarly copy this to other rows. Your sheet should now look something like this:

You can now format these dates so they're more recognizable. Select all these new cells, and right click and choose "format cells...".

You can then pick any format you choose. Keep in mind, this formula we entered doesn't factor in hour. If you want formatted hours and minutes, you'll have to find another formula for that.

Et Voila! You now have some nice friendly dates to work with:

Now you can do all kinds of things like filter by year, etc:

Remember, if the report has an ISO date, the timezone is GMT, which is not likely your timezone. Unfortunately, we don't know a way to convert this in Excel. For this reason, you may have "Term End Dates" that read "1/1/{next-year}", when the term should actually end "12/31/{year}". Just keep that in mind and adjust your filter accordingly. Maybe instead of filtering by just year as seen above, you can filter by a date range.

If you find a report that has ISO dates, ask us if we're able to change that - perhaps we can improve the report in the future - we're trying to phase out this format of dates when exporting data so that all these steps aren't necessary.