Rudy Rodarte . com

Information

This article was written on 24 May 2011, and is filled under SQL Blogs, Windows.

Current post is tagged

, , ,

Working with Seconds in Excel

I recently had to use back end data from SQL Server to deliver several reports in excel. All of my SQL code gave me results in seconds; however, the users had distinct formatting requests. I would have to use a few excel tricks to get things just right in each report.

Time in Seconds to HH:MM:SS

For the first request, I had to take the number of seconds and convert to HH:MM:SS. My script gave me results like 18000 seconds or so. To get the results in the desired format, I first divide the value by 86400. 86400 is the number of seconds in a day (60*60*24). Next, in custom formatting, choose [h]:mm:ss. It is important to choose [h]:mm:ss instead of HH:MM:SS because the h in brackets allows numbers greater than 23. So, our example of 18000 seconds becomes 5 hours.

Time to Decimal Hours

For the second request, I had to take the number of seconds and covert to decimal hours. Let’s look at 5 hours and 45 minutes or 20700 seconds. The first step is to divide the seconds by 86400 and convert the value to [h]:mm:ss. In our example, 20700 seconds appears as 5:45:00. Next, divide the value by 0.041666667. The number 0.041666667 = 1/24th of a day. Lastly, format the results as number with 2 decimal places. The result comes out nicely with number of hours before the decimal and fractional hours after the decimal. So, our example of 20700 seconds becomes 5.75 hours.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter

Leave a Reply