Sunrise, Sunset & Winter Equinox: Creating Your Spreadsheet

In your science class you should have collected the sunrise and sunset times for the past two weeks. In this lab, you will create a spreadsheet and chart to show the hours of sunlight in a day using Excel.

Entering Your Data

  1. Open a new Excel worksheet. Enter your Hall Header information
  2. In column B enter in the date that each measurement was taken. Format this column for dates: Highlight the entire column by clicking on the LETTER at the top of the column. Select Format-Cells (apple-1). Click on the number tab and select Date. Choose the date format of 11/30/04.
  3. In column C enter your sunrise times. Before entering your times format column C for time: Highlight the entire column by clicking on the letter C at the top of the column. Select Format-Cells (apple-1). Click on the number tab and select Time. Choose minutes, seconds and PM. Enter in your sunrise times. Remember that you must enter in your times with a colon and MUST have a space between the time and the AM or PM.
  4. In column D enter your sunset times. Before entering your times format column D for time: Highlight the entire column by clicking on the letter D at the top of the column. Select Format-Cells (apple-1). Click on the number tab and select Time. Choose minutes, seconds and PM. Enter in your sunset times. You will have to enter your sunset times in military time format (16:57) OR manually change the AM to PM. REMEMBER: there must be a space between the time and the AM PM marker.
  5. In column E you will create a formula to calculate the hours of daylight for each day. Before entering your formula format column E for time: Highlight the entire column by clickin on the letter E at the top of the column- select Format-Cells (apple-1). Click on the number tab and select Time. Choose minutes and seconds only. The formula : sunset time minus sunrise time. Remember, all formulas start with an equals symbol. The formula might look like this =D6-C6. Once you have this formula entered once you can use the fill-down function in Excel! Click in the cell with your formula. Highlight down until you reach your last day. Select Edit-Fill-Down from the menu bar to fill down this formula.

Creating Your Chart

  1. Highlight the hours of sunlight for each day. Select only the hours, not the title hours of light.
  2. Select Insert-Chart from the menu bar.
  3. Chart Wizard Step 1: Click on the Custom Types Tab and select B&W Line-Timescale. Click next.
  4. Chart Wizard Step 2: Click next.
  5. Chart Wizard Step 3: Click on the titles tab and enter a title. Click on the data labels tab and select SHOW VALUE. Click finish.
  6. Resize your chart. Move the labels for the hours of sunlight to near the top of the graph by clicking on the time and dragging it to the top of the chart. The time should line up with the date you made each measurement.

Enter Your Observations

  1. Create a text box with the text frame tool from the drawing tool bar.
  2. Describe what you have done. Include the hypothesis, procedure and observations that you have made.
  3. Format your text and spell check.

Printing Your Project

  1. Select File-Print Preview
  2. Click on the Setup tab.
  3. Click on the Page tab and select fit-to: 1 page wide x 1 page tall. Click OK
  4. Click on the Print button to print.