Exceljet

Quick, clean, and to the point

This video is part of our online video training.

How to chart sunrise and sunset times

Tags 
Summary 
In this video, we'll look at how to chart average daylight hours for each month of the year, using sunrise and sunset data. This is a good example of a floating bar technique using a stacked column chart and helper columns.
Video Transcript 

In this video, we'll look at how to chart average daylight hours for each month of the year, using sunrise and sunset data.

This is the final chart.

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.

Author 
Dave Bruns

Related shortcuts