1. ## Lunar calendar? (2002-XP)

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.

2. ## Re: Lunar calendar? (2002-XP)

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?

3. ## Re: Lunar calendar? (2002-XP)

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

4. ## Re: Lunar calendar? (2002-XP)

Hi Peter,

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

Cheers

5. ## Re: Lunar calendar? (2002-XP)

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

6. ## Re: Lunar calendar? (2002-XP)

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
[img]/forums/images/smilies/cool.gif[/img] 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

7. ## Re: Lunar calendar? (2002-XP)

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

Have fun!

Ian.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•