Converting a bunch of numbers into a table

If you paste the table from the website into a new worksheet with the day numbers in column A and the first row of times in row 1, then choose Data->Text to Columns and choose fixed width and click Finish, then the following macro will produce a new worksheet with average sunrise and sunset times for each week of the year. Run the macro with the worksheet containing the imported data active.

To add VBA code to a regular module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Press CTRL+R to open the VBA project explorer. Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

Public Sub ListWeeks()

   Dim TargetSheet As Worksheet
   Dim SunRiseTimes(1 To 366) As Date
   Dim SunSetTimes(1 To 366) As Date
   Dim WeekSunRiseTimes(1 To 53) As Date
   Dim WeekSunSetTimes(1 To 53) As Date
   Dim WeekDate(1 To 53) As Date
   Dim MonthIndex As Long
   Dim DayIndex As Long
   Dim WeekIndex As Long
   Dim Index As Long
   Dim Cell As Range
   Dim CellText As String
   Dim DaysInWeek As Long
   
   Index = 1
   With ActiveSheet
      For MonthIndex = 1 To 12
         For DayIndex = 1 To 31
            Set Cell = .[B1].Offset(DayIndex - 1, MonthIndex * 2 - 2)
            If Len(Cell) > 0 Then
               CellText = Right("0" & Cell, 4)
               SunRiseTimes(Index) = TimeSerial(Left(CellText, 2), Right(CellText, 2), 0)
               CellText = Right("0" & Cell.Offset(0, 1), 4)
               SunSetTimes(Index) = TimeSerial(Left(CellText, 2), Right(CellText, 2), 0)
               Index = Index + 1
            End If
         Next DayIndex
      Next MonthIndex
   End With
   
   WeekIndex = 1
   For DayIndex = 1 To 366
      WeekSunRiseTimes(WeekIndex) = WeekSunRiseTimes(WeekIndex) + SunRiseTimes(DayIndex)
      WeekSunSetTimes(WeekIndex) = WeekSunSetTimes(WeekIndex) + SunSetTimes(DayIndex)
      DaysInWeek = DaysInWeek + 1
      If Weekday(DateSerial(2005, 1, DayIndex)) = vbSunday Then
         WeekSunRiseTimes(WeekIndex) = WeekSunRiseTimes(WeekIndex) / DaysInWeek
         WeekSunSetTimes(WeekIndex) = WeekSunSetTimes(WeekIndex) / DaysInWeek
         WeekDate(WeekIndex) = DateSerial(2005, 1, DayIndex) - 6
         DaysInWeek = 0
         WeekIndex = WeekIndex + 1
      End If
   Next DayIndex
   
   Set TargetSheet = Sheets.Add
   With TargetSheet
      .[A1:D1] = Array("Week", "Starting", "Sun Rise", "Sun Set")
      .[A1:D1].Font.Bold = True
      For WeekIndex = 1 To 53
         If WeekSunRiseTimes(WeekIndex) = 0 Then Exit For
         .[A1:D1].Offset(WeekIndex, 0) = Array(WeekIndex, WeekDate(WeekIndex), WeekSunRiseTimes(WeekIndex), WeekSunSetTimes(WeekIndex))
      Next WeekIndex
      .[A:D].EntireColumn.AutoFit
   End With

End Sub

Kevin