Lunar calendar? (2002-XP)

Topic

New Reply

Is there any Excel function or formula for calculating dates based on the Lunar calendar? I’m trying to create a spreadsheet where I can enter a birthdate and have it return the Chinese zodiac anaimal associated with that birthdate. I have done this successfully except for birthdays that fall just outside the lunar new year. For example, if I enter 1/29/67, it returns RAM/GOAT/SHEEP when, in fact, the animal is HORSE because in 1967, the lunar new year is not until Feb. Hence, 1/29/67 actually falls in Lunar year 1966.

I hope this makes sense… I feel like I’m rambling.

Reply | Quote

Replies

Simple solution may be to put them effective dates and their corresponding names into a table and then use lookup functions to extract them? Depends upon how many years you want to be able to work with. Thousands may be a problem! Alternatively, can you post any code/sheet examples here that may help others understand how you are currently approaching this problem?

Reply | Quote

If the Lunar month is a fixed number of days then you should be able to calculate it easiliy enough. Excel stores it dates as a number of days from a fixed starting point, so assumming that a Lunar month is 28 days and a year is 12 months as long as you know the lunar date for the start the rest is just a calculation.

HTH

Peter

Reply | Quote

Hi Peter,

I believe the lunar month is about 29 days, 12 hrs and 44 minutes +/- a few seconds.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Which means you could add/subtract 29.5305555555555 from a date to get the next/previous lunar month.

Reply | Quote

Hi Legare,
Yes, you could do that, provided:
a) You have a starting date and time to work from to align the Chinese lunar calendar with the solar calendar and lunar cycle; and
the Chinese lunar calendar uses that particular calculation of the duration of the lunar month.
I suspect, though, that Chinese lunar calendar had months/years of different duration, just like the Gregorian calendar does.
Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

There’s a web site that discusses the complexities of the Chinese Lunar Calendars at
http://www.chinesefortunecalendar.com/clc/default.htm “]http:// http://www.chinesefortunecalendar.com/clc/default.htm [/url]

Have fun!

Ian.

Reply | Quote