How to deal with BC and AD dates in Excel or OpenOffice

In the past couple of months, I’ve found myself needing to create timelines and gantt charts that span multiple centuries. Ancient dates aren’t supported by Excel or OpenOffice, so you have to get a little creative. If you only need years, and not month and days, the solution is pretty straightforward:

  1. Enter BC dates as negative numbers, and AD dates as positive numbers.
  2. Select the columns that contain the dates. Right-click, and choose “Format Cells”
  3. On the “Number” tab, choose “Custom,” and type in a number format that adds an AD before the positive numbers and a BC after the negative numbers. In Excel, the┬ácustom number format is:
    "AD" #0;#0 "BC"
  4. Create a stacked bar chart. If you want a timeline, the years should be organized in a single row. If you want a gantt chart, the years should be organized as two columns (first column is start date, second column is duration).

The solution to entering dates like April 1, 1692 is a little uglier, since you have to create an extra column, but whatever:

  1. First column, enter in the month, day (and time, if you need to get that specific)
  2. Format the first column as a Date however you like, showing everything except the year. Once you are fine with your date formatting, make a note of the number format code (shown when you click on the Custom option of the Number tab of the Format Cells dialog). For instance, you would note the string “d-mmm” if you decide your month and day should show up as “1-Apr”.
  3. Second column, enter the year as just a number
  4. Third column, use code similar to the following to concatenate the formatted date and year — replacing A1, B1 with cells from the first and second column respectively. Replace “d-mmm” with the string you noted from step 2.
    CONCATENATE(TEXT(A1,"d-mmm")," ",B1)

Leave a Reply

Your email address will not be published. Required fields are marked *