在Google 日曆設定以農曆為準的循環活動

起因是每年農曆的某一天必須祭拜。在Google 日曆中可以顯示農曆，也可以設定以陽曆日期為準的週期活動。例如每年的陽曆的6月4日來記念一下。但是要設定在每年農曆的七月十五日就只能一年一年自己人工設定，沒辦法自動循環。因為每年的七月十五日對應到的陽曆不都是同一天。例如2021年8月22日是農曆七月十五日。如果設定在這個日期要以年為週期的活動，下次日期會是落在2022年8月22日，但當年的農曆七月十五日卻是在8月12日，對不上了。

嘗試了許多可能後，決定用Excel 產生日曆的CSV檔，再匯入 Google 日曆。

EXCEL並沒有農曆轉陽曆的函數可以直接利用，我選擇的是用VBA的自訂函數來做轉換。這個函數並不是我自己寫的，參考的是這個網頁「

EXCEL-VBA function: Lunar calendar to Gregorian calendar, format YYYY-MM-DD

」。

在Excel中使用VBA，要先把 選項→自訂功能區→開發人員，這個「開發人員」勾選起來。

再來就到VBA中，新增模組，再複製貼上去。

原本的程式是計算自1921年2月8日起100年的時間，也就是到2021年。啊！今年就是2021年了，要用來預定未來行事活動跟本就不夠用。關鍵在程式裡定義的一個陣列變數NongliData(100)，只有100個。還好網路上有人再繼續延伸到179，但奇怪的是隔了2天，我再也找不到原本分享的網站了。

NongliData(101) = 2773

NongliData(102) = 133557

NongliData(103) = 1206

NongliData(104) = 398510

NongliData(105) = 2638

NongliData(106) = 3366

NongliData(107) = 335142

NongliData(108) = 3411

NongliData(109) = 1450

NongliData(110) = 200042

NongliData(111) = 2413

NongliData(112) = 723293

NongliData(113) = 1197

NongliData(114) = 2637

NongliData(115) = 399947

NongliData(116) = 3365

NongliData(117) = 3410

NongliData(118) = 334676

NongliData(119) = 2906

NongliData(120) = 1389

NongliData(121) = 133467

NongliData(122) = 1179

NongliData(123) = 464023

NongliData(124) = 2635

NongliData(125) = 2725

NongliData(126) = 333477

NongliData(127) = 1746

NongliData(128) = 2778

NongliData(129) = 199350

NongliData(130) = 2359

NongliData(131) = 526639

NongliData(132) = 1175

NongliData(133) = 1611

NongliData(134) = 396618

NongliData(135) = 3749

NongliData(136) = 1706

NongliData(137) = 267628

NongliData(138) = 2734

NongliData(139) = 2350

NongliData(140) = 203054

NongliData(141) = 3222

NongliData(142) = 465557

NongliData(143) = 3402

NongliData(144) = 3493

NongliData(145) = 330581

NongliData(146) = 1386

NongliData(147) = 2669

NongliData(148) = 264797

NongliData(149) = 1325

NongliData(150) = 529707

NongliData(151) = 2709

NongliData(152) = 2890

NongliData(153) = 399018

NongliData(154) = 2773

NongliData(155) = 1370

NongliData(156) = 267450

NongliData(157) = 2651

NongliData(158) = 1323

NongliData(159) = 202023

NongliData(160) = 1683

NongliData(161) = 462419

NongliData(162) = 1706

NongliData(163) = 2773

NongliData(164) = 330165

NongliData(165) = 1206

NongliData(166) = 2647

NongliData(167) = 264782

NongliData(168) = 3350

NongliData(169) = 531750

NongliData(170) = 3410

NongliData(171) = 3498

NongliData(172) = 396650

NongliData(173) = 1389

NongliData(174) = 1198

NongliData(175) = 267421

NongliData(176) = 2605

NongliData(177) = 3349

NongliData(178) = 138021

NongliData(179) = 3410

把這一串複製接上以後，別忘了前面宣告的部份也要一併修改 Dim NongliData(100)改成Dim NongliData(180)，這樣可以計算到2101年了，我應該沒有活那麼久了。

回到Excel中，就多了Lunar2Gong()，這個函數可以用了，下面的例子就是A欄是農曆日期，B欄是用Lunar2Gong()轉換成陽曆。有了對應的陽曆就可以複製到要匯入Google日曆的CSV檔。

CSV檔的格式和匯入，網路有許多參考資料。Google網站也有十分清楚的說明。

將活動匯入 Google 日曆

它有8個欄位可以填入。但不是每個欄位都必須具備，最基本只要有Subject和Start Date這兩個欄位就可以匯入了。

Subject：行程名稱。

Start Date：行程的開始日期。

Start Time：行程的開始時間。

End Date：行程的結束日期。

End Time：行程的結束時間。

All Day Event：是否為全天行程。True為是；False為否。

Description：行程附註。

Location：行程地點。

我還希望在設定的日期前一週就先提醒我。但匯入的欄位並沒有這個功能。變通的方法是我把活動匯入獨立的日曆中，再對這個日曆設定預先提醒的功能，只要是這個日曆中的活動都一起適用這個設定，來達到預先提醒的目的。 

下面就是這次用到的程式

 

Public Function Lunar2Gong(Optional Lunar As Date)

    Dim LunarYear, LunarMonth, LunarDay

    Dim NongliData(180)

      'Lunar data

  NongliData(0) = 2635

  NongliData(1) = 333387

  NongliData(2) = 1701

  NongliData(3) = 1748

  NongliData(4) = 267701

  NongliData(5) = 694

  NongliData(6) = 2391

  NongliData(7) = 133423

  NongliData(8) = 1175

  NongliData(9) = 396438

  NongliData(10) = 3402

  NongliData(11) = 3749

  NongliData(12) = 331177

  NongliData(13) = 1453

  NongliData(14) = 694

  NongliData(15) = 201326

  NongliData(16) = 2350

  NongliData(17) = 465197

  NongliData(18) = 3221

  NongliData(19) = 3402

  NongliData(20) = 400202

  NongliData(21) = 2901

  NongliData(22) = 1386

  NongliData(23) = 267611

  NongliData(24) = 605

  NongliData(25) = 2349

  NongliData(26) = 137515

  NongliData(27) = 2709

  NongliData(28) = 464533

  NongliData(29) = 1738

  NongliData(30) = 2901

  NongliData(31) = 330421

  NongliData(32) = 1242

  NongliData(33) = 2651

  NongliData(34) = 199255

  NongliData(35) = 1323

  NongliData(36) = 529706

  NongliData(37) = 3733

  NongliData(38) = 1706

  NongliData(39) = 398762

  NongliData(40) = 2741

  NongliData(41) = 1206

  NongliData(42) = 267438

  NongliData(43) = 2647

  NongliData(44) = 1318

  NongliData(45) = 204070

  NongliData(46) = 3477

  NongliData(47) = 461653

  NongliData(48) = 1386

  NongliData(49) = 2413

  NongliData(50) = 330077

  NongliData(51) = 1197

  NongliData(52) = 2637

  NongliData(53) = 268877

  NongliData(54) = 3365

  NongliData(55) = 531109

  NongliData(56) = 2900

  NongliData(57) = 2922

  NongliData(58) = 398042

  NongliData(59) = 2395

  NongliData(60) = 1179

  NongliData(61) = 267415

  NongliData(62) = 2635

  NongliData(63) = 661067

  NongliData(64) = 1701

  NongliData(65) = 1748

  NongliData(66) = 398772

  NongliData(67) = 2742

  NongliData(68) = 2391

  NongliData(69) = 330031

  NongliData(70) = 1175

  NongliData(71) = 1611

  NongliData(72) = 200010

  NongliData(73) = 3749

  NongliData(74) = 527717

  NongliData(75) = 1452

  NongliData(76) = 2742

  NongliData(77) = 332397

  NongliData(78) = 2350

  NongliData(79) = 3222

  NongliData(80) = 268949

  NongliData(81) = 3402

  NongliData(82) = 3493

  NongliData(83) = 133973

  NongliData(84) = 1386

  NongliData(85) = 464219

  NongliData(86) = 605

  NongliData(87) = 2349

  NongliData(88) = 334123

  NongliData(89) = 2709

  NongliData(90) = 2890

  NongliData(91) = 267946

  NongliData(92) = 2773

  NongliData(93) = 592565

  NongliData(94) = 1210

  NongliData(95) = 2651

  NongliData(96) = 395863

  NongliData(97) = 1323

  NongliData(98) = 2707

  NongliData(99) = 265877

  NongliData(100) = 1706

  NongliData(101) = 2773

NongliData(102) = 133557

NongliData(103) = 1206

NongliData(104) = 398510

NongliData(105) = 2638

NongliData(106) = 3366

NongliData(107) = 335142

NongliData(108) = 3411

NongliData(109) = 1450

NongliData(110) = 200042

NongliData(111) = 2413

NongliData(112) = 723293

NongliData(113) = 1197

NongliData(114) = 2637

NongliData(115) = 399947

NongliData(116) = 3365

NongliData(117) = 3410

NongliData(118) = 334676

NongliData(119) = 2906

NongliData(120) = 1389

NongliData(121) = 133467

NongliData(122) = 1179

NongliData(123) = 464023

NongliData(124) = 2635

NongliData(125) = 2725

NongliData(126) = 333477

NongliData(127) = 1746

NongliData(128) = 2778

NongliData(129) = 199350

NongliData(130) = 2359

NongliData(131) = 526639

NongliData(132) = 1175

NongliData(133) = 1611

NongliData(134) = 396618

NongliData(135) = 3749

NongliData(136) = 1706

NongliData(137) = 267628

NongliData(138) = 2734

NongliData(139) = 2350

NongliData(140) = 203054

NongliData(141) = 3222

NongliData(142) = 465557

NongliData(143) = 3402

NongliData(144) = 3493

NongliData(145) = 330581

NongliData(146) = 1386

NongliData(147) = 2669

NongliData(148) = 264797

NongliData(149) = 1325

NongliData(150) = 529707

NongliData(151) = 2709

NongliData(152) = 2890

NongliData(153) = 399018

NongliData(154) = 2773

NongliData(155) = 1370

NongliData(156) = 267450

NongliData(157) = 2651

NongliData(158) = 1323

NongliData(159) = 202023

NongliData(160) = 1683

NongliData(161) = 462419

NongliData(162) = 1706

NongliData(163) = 2773

NongliData(164) = 330165

NongliData(165) = 1206

NongliData(166) = 2647

NongliData(167) = 264782

NongliData(168) = 3350

NongliData(169) = 531750

NongliData(170) = 3410

NongliData(171) = 3498

NongliData(172) = 396650

NongliData(173) = 1389

NongliData(174) = 1198

NongliData(175) = 267421

NongliData(176) = 2605

NongliData(177) = 3349

NongliData(178) = 138021

NongliData(179) = 3410

 

  Dim m, monthCount, toCurMonthCnt, LeapMonth, TheDate, curTime

  Dim i1, i2, i3, bit

  

     'Get the current variable date

  curTime = Lunar

  LunarYear = Year(curTime)

  LunarMonth = Month(curTime)

  LunarDay = Day(curTime)

 

  m = LunarYear - 1921

  monthCount = 0

  toCurMonthCnt = 0

  LeapMonth = -1

  TheDate = LunarDay - 1

 

    For i1 = 0 To m - 1 Step 1

        If (NongliData(i1) < 4095) Then

            monthCount = 11

        Else

            monthCount = 12

        End If

        For i2 = 0 To monthCount Step 1

            bit = NongliData(i1)

            For i3 = 1 To i2 Step 1

                    bit = Int(bit / 2)

            Next

            bit = bit Mod 2

            TheDate = TheDate + 29 + bit

        Next

    Next

   

    If (NongliData(m) < 4095) Then

        monthCount = 11

        toCurMonthCnt = monthCount - LunarMonth + 2

    Else

        monthCount = 12

        toCurMonthCnt = monthCount - LunarMonth + 1

        LeapMonth = Int(NongliData(m) / 65536)

        If LunarMonth <= LeapMonth Then toCurMonthCnt = toCurMonthCnt + 1

    End If

   

    For i2 = monthCount To toCurMonthCnt Step -1

        bit = NongliData(m)

        For i3 = 1 To i2 Step 1

                bit = Int(bit / 2)

        Next

        bit = bit Mod 2

        TheDate = TheDate + 29 + bit

    Next

   

    Lunar2Gong = DateAdd("d", TheDate, "1921/2/8")

End Function

A thousand years Christina Perri

 A thousand years Christina Perri

(David Hodges / Christina Perri)

Heart beats fast

Colors and promises

How to be brave

How can I love when I'm afraid to fall

But watching you stand alone

All of my doubt suddenly goes away somehow

One step closer

*I have died everyday, waiting for you

Darling, don't be afraid

I have loved you for a thousand years

I'll love you for a thousand more

Time stands still

Beauty in all she is

I will be brave

I will not let anything take away what's standing in front of me

Every breath, every hour has come to this

One step closer (*)

**And all along I believed I would find you

Time has brought your heart to me

I have loved you for a thousand years

I'll love you for a thousand more

One step closer

One step closer (*,**)