This is the final chart.
This project has a couple of interesting challenges.
First let’s look at the available data.
you’re able to see we have data for both sunrise and sunset.
Both columns contain valid Excel times.
The clustered column option isn’t really useful.
Stacked columns are closer to what we want.
There are several ways to approach this.
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.
This means I can subtract the sunset time from 1 to get evening hours.
First, I’ll give the chart a title, and delete the legend, and adjust colors.
Next, I’ll format the y-axis to line up on the clock.
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.
