| View previous topic :: View next topic |
| Author |
Message |
ShounenSuki
Newbie
Joined: 25 Apr 2006
Posts: 2
|
 Posted: Tue Apr 25, 2006 3:10 pm Post subject: BCE dates and Japanese eras |
 |
|
Is there an easy way to enter BCE dates?
Also, is it possible to extend the the list of Japanese eras OOCalc knows? So far, it only knows the last 4 (Meiji, Taishou, Shouwa, and Heisei). |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Apr 26, 2006 2:30 am Post subject: |
|
|
Hi,
I guess, most members of this forum have long noses. As far as I am concerned, I beg your pardon for my total ignorance on asian calendars.
You may find useful information in one of the japanese mailing lists at
http://ja.openoffice.org/servlets/ProjectMailingListList
If you want to lookup the japanese name of a gregorian year you may use something like this:
[copied list from wikipedia]
1394 Ōei
1428 Shōchō
1429 Eikyō
1441 Kakitsu
1444 Bunnan
1449 Hōtoku
1452 Kyōtoku
1455 Kōshō
1457 Chōroku
1460 Kanshō
1466 Bunshō
1467 Ōnin
1469 Bunmei
1487 Chōkyō
1489 Entoku
1492 Meiō
1501 Bunki
1504 Eishō
1521 Daiei
1528 Kyōroku
1532 Tenbun
1555 Kōji
1558 Eiroku
1570 Genki
1573 Tenshō
1592 Bunroku
1596 Keichō
1615 Genna
1624 Kan'ei
1644 Shōhō
1648 Keian
1652 Jōō
1655 Meireki
1658 Manji
1661 Kanbun
1673 Enpō
1681 Tenna
1684 Jōkyō
1688 Genroku
1704 Hōei
1711 Shōtoku
1716 Kyōhō
1736 Genbun
1741 Kanpō
1744 Enkyō
1748 Kan'en
1751 Hōreki
1764 Meiwa
1772 An'ei
1781 Tenmei
1789 Kansei
1801 Kyōwa
1804 Bunka
1818 Bunsei
1830 Tenpō
1844 Kōka
1848 Kaei
1854 Ansei
1860 Man'en
1861 Bunkyū
1864 Genji-Zeit
1865 Keiō
1868 Meiji-Zeit
1912 Taishō
1926 Shōwa
1989 Heisei
I named the list "jpEra"
A gregorian date in E2: 1580-10-03
Formula in F2:
| Code: |
=VLOOKUP(YEAR($E2);jpEra;2;1)&" "&YEAR($E2)-VLOOKUP(YEAR($E2);jpEra;1;1)+1
|
Gives "Tenshō 8". This does not format your date-cells, but at least it gives a year-name in another cell.
G2: Genna
H2: 15
| Code: |
I2: =INDEX(jpEra;MATCH($G2;INDEX(jpEra;0;2);0);1)-1+$H15
|
gives gregorian year 1614
Please notice: Any date-value in a spreadsheet is nothing than a number, representing the days since start of "spreadsheet-epoch". Day zero in calc is 1899-12-30. When a cell A1 shows a date or time, you retrieve it's real numeric value by using =N(A1).
=N(TODAY()) gives 38833 meaning 38833 days since 1899-12-30
=N(NOW()) gives 38833.5 (half a day at 12 pm)
=YEAR(0) gives 1899
=YEAR(-115858) gives 1582, the year of 115858 days before 1899-12-30
Input of 1582-10-05 to 1582-10-14 is not recognized as a date. It gives a text-value because of the gregorian calendar reform.
Thursday 1582-10-04 (day -115857)
is followed directly by
Friday 1582-10-15 (day -115858)
I hope this helps a little. |
|
| Back to top |
|
|
ShounenSuki
Newbie
Joined: 25 Apr 2006
Posts: 2
|
| Posted: Thu Apr 27, 2006 2:13 am Post subject: |
|
|
| Thank you, this really helps a lot ^^ |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Apr 27, 2006 8:54 am Post subject: |
|
|
| Quote: | | Is there an easy way to enter BCE dates |
I forgot to answer your first question. I don't know. I tried this:
I set "Two Digit Years" (Menu:Tools>Settings>OOo>General) to zero. When you apply this and recall the dialog you can see that it has been set to 1583-1682 actually.
Calc seems to refuse dates before gregorian reform in cell-functions. =DATE(0;1;1) -> 1600-01-01, cheating with =DATE(0; Month;1) fails with Month < -205 (err #VALUE because it would be a date before gregorian reform).
Three and four-digit years before 1582-10-04 work when you type them into a cell, but you can't compare them exactly with european historical records because the year-length is gregorian (interestingly 996-2-29 is a valid date -328652, but =ISLEAPYEAR(996-2-29) = False).
I found no way to enter BCE-dates and I'm not shure about the implications on the lookup-solution I tried before. This depends on the assumed year-length when creating such a lookup-list.
If I where a european, working on historical records, I would implement some userdefined function converting a negative number <1582-10-15 to a Julian date-string for comparison with actual historical records or I would use some database, able to handle this. |
|
| Back to top |
|
|
|
