Excel formula to convert Ethiopian calendar dates to Gregorian dates

ethiopian-to-gregorian-in-excel.md

Convert Ethiopian dates to Gregorian dates in Excel

Formula

This formula operates on an Ethiopian date in cell A2. It assumes that cell uses a mm/dd/yyyy format, where the months are 1-13.

= IF(ISBLANK(A2), "", DATE(RIGHT(TEXT(A2, "mm/dd/yyyy"), 4) + 8, 1, 1) + ((LEFT(TEXT(A2, "mm/dd/yyyy"), 2) * 30) - 120 + MID(TEXT(A2, "mm/dd/yyyy"), 4, 2) - IF(MOD(RIGHT(TEXT(A2, "mm/dd/yyyy"), 4) + 1, 4) <> 1, 23, 22)))

Here's a more readable version of the same formula:

=
IF(ISBLANK(A2),
  "",
  DATE(RIGHT(TEXT(A2, "mm/dd/yyyy"), 4) + 8, 1, 1)
  +
  (
    (LEFT(TEXT(A2, "mm/dd/yyyy"), 2) * 30)
    - 120
    + MID(TEXT(A2, "mm/dd/yyyy"), 4, 2)
    - IF(MOD(RIGHT(TEXT(A2, "mm/dd/yyyy"), 4) + 1, 4) <> 1, 23, 22)
  )
)

Context

Ethiopia uses their own calendar which differs from the Gregorian calendar in the following ways:

• Their calendar is 7 years, 8 months, and 11 days behind the Gregorian calendar (12 days if it's an Ethiopian leap year).
• It uses 13 months. 12 months of 30 days each, and a 13th month with 5 days (or 6 days for leap years).
• Ethiopian leap years are every 4 years, without exceptions. They occur the year before Gregorian leap years, so 1999 and 2003 are leap years in Ethiopia.

How this works

1. Create a new date on January 1, 8 years ahead of the Ethiopian year
2. Determine the offset to add or subtract:
   1. Offset is Ethiopian month * 30 days
   2. Subtract 120 days
   3. Add Ethiopian days
   4. Subtract 23 days (or 22 days if the it's year AFTER an Ethiopian leap year)

Why the weird offset for leap years?

The first 4 months of the Ethiopian year will result in a negative offset, since we're subtracting 120 days. When you subtract days from a Gregorian year, you need to subtract a different number of days if the Gregorian year is a leap year. Since those occur the year after an Ethiopian leap year, we only subtract 22 days then.

Test data

Ethiopian Date	Gregorian Date	Days offset
01/01/2011	09/11/2018	-112
02/01/2011	10/11/2018	-82
03/01/2011	11/10/2018	-52
04/01/2011	12/10/2018	-22
05/01/2011	01/09/2019	8
06/01/2011	02/08/2019	38
07/01/2011	03/10/2019	68
08/01/2011	04/09/2019	98
09/01/2011	05/09/2019	128
10/01/2011	06/08/2019	158
11/01/2011	07/08/2019	188
12/01/2011	08/07/2019	218
13/05/2011	09/10/2019	252
01/01/2012	09/12/2019	-111
02/01/2012	10/12/2019	-81
03/01/2012	11/11/2019	-51
04/01/2012	12/11/2019	-21
05/01/2012	01/10/2020	9
06/01/2012	02/09/2020	39
07/01/2012	03/10/2020	69
08/01/2012	04/09/2020	99
09/01/2012	05/09/2020	129
10/01/2012	06/08/2020	159
11/01/2012	07/08/2020	189
12/01/2012	08/07/2020	219
13/05/2012	09/10/2020	253