Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lunar calendar? (2002-XP)

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

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •