PhDr. Mgr. Jeroným Klimeš, Ph.D.
www.klimes.us; jeronym.klimes@gmail.com
There is no function in LibreOffice Calc, that will convert UTC date BC into internal Calc integer.
Say we have UTC date “ -9-11-22 00:00:00.000000 UTC” which means “0010-11-22 BC”, i.e. year 10 before our epoch, so called ”before Christ”, month November 22nd. This number is to be transformed into integer: -696923, which means, that this day “0010-11-22 BC” is 696923th day before the date 1990-01-01 AD.
Calc further works with his internal representation of the date as with a number and so it plots dates in scale in charts.
If we want to plot correctly number -696923 as “0010-11-22 BC”, we must use format formula and enter in Cells properties: “YYYY-MM-DD GG”
As there is no function to convert year-month-day epoch into a integer representation, I spend a few days to make following seemingly simple formula:
DATE(2005+year -1,1,1)-731948+DATE(2001+year-1,month,day)-DATE(2001+year-1,1,1)
There is a big problem with leaps years and with revers counting of days in years BC. So a simple explanation of the formula:
|
year -1 |
-09-11-22 is 0010-11-22 BC, so the year must be shifted |
|
DATE(2005+year -1,1,1) |
This creates integer of January 1st in 2004-year |
|
DATE(2005+year -1,1,1)-731948 |
This shifts the previous date into “January 1st YEAR-1” |
|
DATE(2001+year-1,month,day)-DATE(2001+year-1,1,1) |
It counts number of days since the beginning of the given BC year. The point is that it must respect leap years, so it works correctly up to 103 BC |
Download this Calc table to try yourself (calc_bcdate.ods).
This formula works correctly up to 100 BC, so there is very much space left for you imprevement. So thank you for your hints.