This is the final chart. This project has a couple of interesting challenges. First let’s look at the available data. You can see we have data for both sunrise and sunset. Both columns contain valid Excel times. Now, if I try to create a column chart with just this data, we’ll have some problems. The clustered column option isn’t really useful. Stacked columns are closer to what we want. But, if you look closely, you can see that both sunrise and sunset are plotted as durations, instead of starting points, which isn’t going to work. There are several ways to approach this. Personally, I like to use helper columns, since helper columns let you to work directly on the worksheet with formulas, and you can work step by step. First, we need a column for total daylight hours. This is pretty simple, I can just subtract sunrise from sunset. Both values are times, so the result can also be formatted as time. However, I don’t want AM/PM, just hours and minutes. Now I can plot daylight hours stacked on sunrise times. Notice I am not plotting sunset data. To add hours between sunset and midnight, I need another helper column for evening hours. The formula is simple but the concept is a little tricky. Excel time is recorded as fractions of a day. Because there are 24 hours in a day, one hour is 1/24, and 24 hours is 24/24, or 1. This means I can subtract the sunset time from 1 to get evening hours. When I add evening hours to the chart as another data series, we have a sensible chart and can tidy things up. First, I’ll give the chart a title, and delete the legend, and adjust colors. Then I’ll bump up the column width and add data labels to show total daylight hours directly on the chart. Next, I’ll format the y-axis to line up on the clock. 3 hours in Excel is 3/24, or .125 If I set max to 1 for 24 hours, and use .125 as the major unit, the axis shows 3 hour increments, which is pretty easy to read. I can use number formatting to show hours only. Finally, I can make daylight hours easier to read by converting to decimal values in another helper column. The formula is 24 * daylight hours. Back in the chart, I’ll can change data labels to show these new decimal hours. And we have our final chart.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
