Transposing Sun and Moon rise and set times...

[greg.mouning]

Hello,

I have data that represents, rise and set times for the sun and moon
over a time span of 12 months. Data for each consecutive month appear
in joining columns as follows:

January February
Day Sun_Rise Sun_Set Moon_Rise Moon_Set
Sun_Rise Sun_Set Moon_Rise
Moon_Set
1 0700 1625 1400 0255 0705 1710 0901 2114
2 0701 1625 1429 0413 0704 1711 0924 2229
3 0702 1625 1504 0532 0703 1713 0947 2343
4 0703 1625 1548 0650 0702 1714 1013
5 0704 1625 1642 0803 0701 1715 1042 0056
6 0705 1625 1745 0905 0700 1716 1117 0207
7 0706 1625 1854 0955 0659 1718 1159 0315
8 0707 1624 2003 1034 0657 1719 1249 0416
9 0708 1625 2110 1104 0656 1720 1346 0509
10 0708 1625 2214 1129 0655 1721 1448 0552
11 0709 1625 2316 1150 0654 1723 1552 0627
12 0710 1625 1209 0652 1724 1655 0656
13 0711 1625 0016 1227 0651 1725 1758 0720
14 0712 1625 0116 1246 0650 1726 1859 0740
15 0712 1625 0217 1306 0649 1728 1959 0759
16 0713 1626 0320 1329 0647 1729 2100 0817
17 0714 1626 0426 1358 0646 1730 2202 0836
18 0714 1626 0533 1433 0645 1731 2306 0856
19 0715 1627 0640 1518 0643 1733 0919
20 0715 1627 0742 1614 0642 1734 0013 0947
21 0716 1628 0836 1721 0640 1735 0122 1023
22 0716 1628 0921 1834 0639 1736 0232 1109
23 0717 1629 0957 1949 0637 1737 0338 1208
24 0717 1629 1027 2104 0636 1739 0436 1320
25 0718 1630 1053 2218 0634 1740 0523 1440
26 0718 1631 1116 2331 0633 1741 0601 1603
27 0718 1631 1139 0631 1742 0632 1726
28 0719 1632 1202 0044 0630 1743 0659 1846
29 0719 1633 1229 0158
30 0719 1634 1300 0315
31 0719 1634 1339 0431

Does Excel have a way to format this data for viewing on a calendar?
For example:

January 1 January 2 January 3
Rise Set Rise Set Rise Set
Sun: 0700 1625 0701 1625 0702 1625
Moon: 1400 0255 1429 0413 1504 0532

Or perhaps someone can explain how best to transpose this data so that
it can be used as Mail Merge data on a Microsoft Word Calendar
template?

Thanks in advance for your responses.

-Greg

 

[Jim Thomlinson]

My recommendation would be to rearrange the data as follows:

Date SunRise SunSet MoonRise MoonSet
Jan 1 8:00 20:00 17:00 7:00
Jan 2 ...

From here you can pivot the data with a pivot table,allowing you to
reorganize the data to appear however you want. Keep in mind that the days
running across the columns will not work as there are 365 days and only 256
columns...

 

[greg.mouning]

Hi Jim,

Thanks for your prompt reply and recommendation. I'm not very advance
when it comes to using Excel, can you explain how to do that? I
stumbled across the SNAKECOLS webpage
(http://www.mvps.org/dmcritchie/excel/snakecol.htm) but am unclear
which macro/function will serve my purpose.

Regards,
Greg

 

[Jim Thomlinson]

I was refering to a simple physical re-arrangement of the source data into
the format that I showed. By simply moving the blocks of cells around it
should not take more than a couple of minutes to get the data into the proper
5 colum format. Once that is done then you can click Data -> Pivot Table and
follow the wizard to create the table. Make sure that your dates are actual
dates and not just text. You can then group on the dates and group by month
and day. Place the date in the row acress the top of the Pivot Table. Place
the times in the middle (these must be actual times not text). Place the
month in the top left corner. You will need to remove any subtotals that may
be inserted by right clicking on the time and change the totaing option to
none. You may also need to change the Table options to remove any subtotals.
This can be done from the pivot table tool bar.

If you want a code solution for this, that is a pile more work. The post
that you show is going to require an understanding of code that is probably
over your head. If you want to download it and take a stab at we can help you
with any specific issues you may have.
--
HTH...

Jim Thomlinson

Hi Jim,

Thanks for your prompt reply and recommendation. I'm not very advance
when it comes to using Excel, can you explain how to do that? I
stumbled across the SNAKECOLS webpage
(http://www.mvps.org/dmcritchie/excel/snakecol.htm) but am unclear
which macro/function will serve my purpose.

Regards,
Greg

 

[greg.mouning]

Jim,

Thanks again for your reply but I've decided to take a different
approach to this same problem. I have downloaded one of the monthly
Excel Calendar Templates
(http://office.microsoft.com/en-us/templates/TC011646221033.aspx?CategoryID=CT011653371033).
Instead of adding holidays and birthday data, I would like to populate
it with my SunRise, SunSet, MoonRise & MoonSet data.

Can you tell me how best to accomplish this task? Thanks in advance for
any
assistance you can provide.

-Greg