Charting Seasonal Change Data

Step by step instructions for finding, downloading and manipulating sunrise/sunset data in Excel.

Finding data:

1. From the U.S. Naval Observatory at http://aa.usno.navy.mil/AA/    click on data services.

2. Choose:   Table of Sunrise/Sunset, Moonrise/Moonset, or Twilight Times for an entire year.

3. Use Form A for anyplace in the U.S. and compute table. All times will be given in standard time, not daylight savings time.

4. Use Form B for any place outside the U.S. - you must fill in the latitude and longitude to get the correct data.
Example: Santiago, Chile:
      Latitude = 33° 27' 00"South         Longitude = 70° 40' 00" West
Also if you don't fill in the time zone conversion your times will be in Universal Time (Zulu or Greenwich) not Chilean standard time which will make it look like the sun is rising and setting very late. Chile is 4 hours West of Greenwich.

Downloading data into Excel:

5. Save the whole page or highlight desired data on the Naval Observatory web page.

6. Save as a text (.txt) file.

7. Open Excel.

8. Open your txt file: a text import wizard should pop up.

9. Scroll the wizard's window so that you can see the columns of sunrise and set times.

10. Choose fixed width - this data is separated into columns. Press next.

Getting the data into a time format:

11. Click to add a column separater between the hours and minutes of each time all the way across the spreadsheet. (You may need 24 or more new separators.) In step 12 you will recombine these with a formula so that correct time calculations can be read.

12. Scroll across slowly to be sure you have separated the columns all the way across. Then click next.

13. The format is general at this point. Click finish.

You need to get the data lined up vertically to make your chart. You have a number of options here. You can work with every day for a whole year or you can sample several data points for each month.

14. Copy and paste the data in month sized blocks. Put February below January. Then move March below February and continue so that you have data for these dates vertically for the whole year on your worksheet. Add labels for the dates in column 1.

15. Use the time function to combine column B(hours) and column C(minutes) for sunrise times and columns D and E for sunset times.
The formula =(TIME(B3, C3,0)) will create a time value by getting the hour from B3, the minutes from C3 and putting in 0 for the seconds.

16. Use copy and paste to copy this formula down the column.

Here is an example of how the top of your spreadsheet might look:

17. Now that your data is in time format, the computer can calculate daylength by subtracting the time of sunrise from the time of suset. Don't forget to change the format to a 24 hr time format.

18. Change in daylength from day to day can also be calculated by subtracting the daylenght each day from the next day's length. Since negative time does not make sense to the computer use the absolute value function so you don't get errors when the days start getting shorter.

19. Here is how part of your spreadsheet might look with the formulas showing. To show the formulas instead of the values check formulas under the Tools>Options>View Menu.

20. You can add columns for daylength in other places to organize your data into charts.

21. Highlight the three columns of daylengths.
Click on the chart wizard icon.

22. In step 1 of the chart wizard select a scatterplot with smoothed lines and click next.

23. In step 2 of the chart wizard on the data range page be sure the columns button is clicked. On the series page you can adjust the data ranges. You can type in the actual name of the series or a location where the name can be found on the worksheet.

24. In step 3 you can add a title and label your axes. In step 4 you can choose to put the graphic on a separate page or as an object in your data sheet.

25. This graphic still wouldn't be very clear. Click on the Y axis and the scale tab. Change the minimum to 7:00 (this is time format, the computer will convert it to a decimal format for you.) Change the maximum to 16:00 and the major unit to 1:00.

26. Then click on the Number Tab and change to a custom hour format.

27. Click on the X axis and the scale tab. Change the minimum to 0 and the maximum to 365 since the year has 365 days. Change the major unit to 60. Then click on the number tab. Choose the custom format option. Change the type to mmm to get a three letter abbreviation of the month. Now your graph should look like this:

Further changes can be made to the background, placement of titles, labels and legends, weight of the data lines etc. to achieve any desired effect.

Top of Page Seasonal Change Lesson

1.	From the U.S. Naval Observatory at http://aa.usno.navy.mil/AA/ click on data services.
2.	Choose: Table of Sunrise/Sunset, Moonrise/Moonset, or Twilight Times for an entire year.
3.	Use Form A for anyplace in the U.S. and compute table. All times will be given in standard time, not daylight savings time.
4.	Use Form B for any place outside the U.S. - you must fill in the latitude and longitude to get the correct data. Example: Santiago, Chile: Latitude = 33° 27' 00"South Longitude = 70° 40' 00" West Also if you don't fill in the time zone conversion your times will be in Universal Time (Zulu or Greenwich) not Chilean standard time which will make it look like the sun is rising and setting very late. Chile is 4 hours West of Greenwich.

5.	Save the whole page or highlight desired data on the Naval Observatory web page.
6.	Save as a text (.txt) file.
7.	Open Excel.
8.	Open your txt file: a text import wizard should pop up.
9.	Scroll the wizard's window so that you can see the columns of sunrise and set times.
10.	Choose fixed width - this data is separated into columns. Press next.

11.	Click to add a column separater between the hours and minutes of each time all the way across the spreadsheet. (You may need 24 or more new separators.) In step 12 you will recombine these with a formula so that correct time calculations can be read.
12.	Scroll across slowly to be sure you have separated the columns all the way across. Then click next.
13.	The format is general at this point. Click finish.

14.	Copy and paste the data in month sized blocks. Put February below January. Then move March below February and continue so that you have data for these dates vertically for the whole year on your worksheet. Add labels for the dates in column 1.
15.	Use the time function to combine column B(hours) and column C(minutes) for sunrise times and columns D and E for sunset times. The formula =(TIME(B3, C3,0)) will create a time value by getting the hour from B3, the minutes from C3 and putting in 0 for the seconds.
16.	Use copy and paste to copy this formula down the column.

17.	Now that your data is in time format, the computer can calculate daylength by subtracting the time of sunrise from the time of suset. Don't forget to change the format to a 24 hr time format.
18.	Change in daylength from day to day can also be calculated by subtracting the daylenght each day from the next day's length. Since negative time does not make sense to the computer use the absolute value function so you don't get errors when the days start getting shorter.
19.	Here is how part of your spreadsheet might look with the formulas showing. To show the formulas instead of the values check formulas under the Tools>Options>View Menu.

21.	Highlight the three columns of daylengths. Click on the chart wizard icon.
22.	In step 1 of the chart wizard select a scatterplot with smoothed lines and click next.
23.	In step 2 of the chart wizard on the data range page be sure the columns button is clicked. On the series page you can adjust the data ranges. You can type in the actual name of the series or a location where the name can be found on the worksheet.