Formula to convert a gregorian date to a chinese date

  • Hi


    I am trying to understand that there is a difference the way chinese calculate thei r new year


    the Chinese method, if anyone is born before the 1st 10 days of Feb, he or she belongs to the previous year.


    Western Calender: 1st Jan to 31st Dec.


    Chinese Year: Any dates between late Jan or 1st 10 days of Feb. To the next year Jan-Feb (1st 10 days Feb).


    The 12 months are shifted to start in late Jan or Feb.


    Eg a person born on 11 Jan 1965, his Chinese date is 16 Dec 1964. So the Kua number has also changed. So the accuracy is out.


    is there a way that a formula can be used to achieve this


    Thank you in anticipation for the help


    kind regards


    rajan

  • Hello,


    If you have available all the rules ...should be feasible ...:)


    Would you surprised to learn this issue has not yet been dealt with ...


    see : https://spreadsheetpage.com/calendar/chinese/

    or : https://china.workingdays.org/…hinese_date_converter.php


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim

    Changed the title of the thread from “Formula to connvert a gregorian date to a chinese date” to “Formula to convert a gregorian date to a chinese date”.
  • Hello again,


    Your question has aroused my interest ....


    Because the Chinese Calendar is very complex (and based on astronomical considerations) ...

    there is no simple way to calculate the Gregorian date of a Chinese New Year which is correct in all cases.


    For the underlying theory, see link below with a Swiss analysis :


    https://www.hermetic.ch/cal_stud/chinese_cal.htm


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi


    Thank you so much for the links makes interesting reading


    I was looking for a way that my spread would be able to convert now realise its not that

    easy


    Probably the only way would be to do it manually and then add into the spreadsheet


    its very interesting because in the western way amy friends kua number was 4 but in the chinese calender his kua number is 3


    its to do with the lunar calendar and the west always start1 st january but the chinese can be mid jan early feb


    Thanx a span for the links I have learnt


    regards


    Rajan

  • Hi,


    Will take a look at your link ...


    However, most probably building a formula will not be possible ... but who knows ...???;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • @khanaran


    Looked at the site you posted, and found the 2021 Calendar with the equivalence between gregorian date and chinese date ...


    but nowhere the calculations rules which might help in creating a formula ...


    Could you indicate where precisely you have found these rules ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi


    This is the link where a formula may be found


    Feng Shui Directions Chart & Calculator, Find Your Lucky Directions by Kua Number, Gua Number (yourchineseastrology.com)


    How To Calculate Feng Shui Kua Number?

    Kua Number for Male:

    1. Add the last two numbers of your year of birth. If it's 0, use 10 instead.

    2. Then, subtract 1 from the number you get if you were born before the Chinese Lunar New Year. (Simply use the Chinese calendar to check if your birthday is before the New Year). If were born after the Chinese Lunar New Year, just skip this step and go on.

    3. As the result needs a single digit, if you already got a single digit, go to the next step. If it contains two numbers, you need to add the numbers you get together.

    4. If you were born before 2000, subtract the single digit you get from 10. If you were born after 2000, subtract the single digit from 9. If the result is a double digit, then add the digits to get a single digit number. Then you get your Kua number finally.

    Kua Number for Female:

    1. Add the last two numbers of your year of birth. If it's 0, use 10 instead.

    2. Then, subtract 1 from the number you get if you were born before the Chinese Lunar New Year. (Simply use the Chinese calendar to check if your birthday is before the New Year). If were born after the Chinese Lunar New Year, just skip this step and go on.

    3. As the result needs a single digit, if you already got a single digit, go to the next step. If not, you need to add the two digits together to get.

    4. If you were born before 2000, add 5 to the single digit. If you were born after 2000, add 6 instead. If the result is a double digit, then add the digits to get a single digit number. Now, you get your Kua number finally.

    Take a man born in 01/16/1982 for an example. The formula is:

    ● 8+2=10

    ● As the Chinese New Year of 1982 started from Jan. 25, 1982, his birthdate is before the New Year: 10-1 = 9

    ● 10-9=1

    Result: 1 is the Kua number for the man.


    it also has tables


    regards


    raj

  • Hi,


    Since you have found the rules you want to apply ... could you draft a sample spreadsheet ...


    Then, it will be easier to spot the different obstacles ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Since you are dealing with a topic that you do master ... most probably, you will be able to overcome the vast majority of the different obstacles you might be facing ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for your file :)


    As soon as I have a moment, will take a look at the rules you have prepared ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Let me clarify one point :


    Do we agree that last two digits of the date of birth are located in cells R4 and S4 ....


    and not in cells L4 and M4 as you indicated ...


    Moreover, an important Remark :


    please NEVER NEVER use MERGED Cells :thumbdown::cursing: .... they will only create HUGE headaches for you ... !!!:evil:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the formula, feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Greetings thank you for the formula its working , however from the chines table, there seems to be a problem not with your formula, but I think the way it was explained and my Malasian friend said the way the Indians work it out is not based on the chinese almanac


    I included the table, will there be a way to write formula that will be able to read from the table


    and indicate the kua number and its direction



    Kind regards


    Rajan

  • Glad to hear the formula does work as expected :)


    Regarding your new question, your Table is made of Text ...


    so Column AF cannot be used for any calculations ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)