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.
