OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

BCE dates and Japanese eras

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
ShounenSuki
Newbie
Newbie


Joined: 25 Apr 2006
Posts: 2

PostPosted: Tue Apr 25, 2006 3:10 pm    Post subject: BCE dates and Japanese eras Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Apr 26, 2006 2:30 am    Post subject: Reply with quote

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
View user's profile Send private message
ShounenSuki
Newbie
Newbie


Joined: 25 Apr 2006
Posts: 2

PostPosted: Thu Apr 27, 2006 2:13 am    Post subject: Reply with quote

Thank you, this really helps a lot ^^
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 27, 2006 8:54 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group