We use cookies on our website to ensure we provide you with the best experience on our website. By using our website, you agree to the use of cookies for analytics and personalized content.This website uses cookies. More Information
It seems like your browser didn't download the required fonts. Please revise your security settings and try again.
Barracuda Web Security Gateway

How can I convert UNIX timestamps to a human-readable format in Excel when exporting CSV reports from my Barracuda Web Filter?

  • Type: Knowledgebase
  • Date changed: 9 years ago

Solution # 00004174

Scope:
This solution applies to all Barracuda Web Filters.


Answer:
It is possible to convert UNIX timestamps to human-readable format in Excel when exporting CSV reports.

The formula used for UNIX timestamps:

 

=(Unix time stamp/86400) + 25569 {-/+} ( TIMEZONE[in GMT] / 24)


Example:

 

=(1247586220/86400)+25569-(8/24) >> = (unix timestamp / number of seconds) + seconds in date[1-1-1970 @ 0:00 hours] - or + (timezone of the device / 24 )

 

  1. Open CSV in Excel and create two columns next to the UNIX timestamp.

  2. Enter the above formula into the cell next to the UNIX timestamp of the first line. Make sure you target the UNIX time stamp by Cell ID (i.e. C1, A2, B4, etc.).

    Example: = (D1/86400) + 25569 - (8/24)
    Note: this is for Pacific time

    Example: = (D1/86400) + 25569 + (3/24)
    Note: this is for Bulgaria time

  3. Once you have entered this into the second column you will get an oddly formatted number.

    Example: = 40008.3199

  4. Once you get this number, highlight the cell and drag the cell so that the formula auto-fills and replicates throughout the column till the last cell of your entries.

  5. Highlight the column, and then right-click > format cell > DATE.

  6. The cell column will format to a date format that you specify.

  7. The last empty column's formula will be simple. Just target the column with the dates specified and replicate this to auto-fill down the column.

  8. If done correctly, you should have duplicate information on both columns. Once that is done, highlight the whole replicated column, and format the cells for TIME.
Link to this page:
https://campus.barracuda.com/solution/50160000000HmS2AAK