# How to convert "standard" dates (Gregorian calendar) to Ethiopian calendar?

Is there a way to convert normal dates in a Google Sheet to the Ethiopian calendar (13 month, around 7 years back) and vice versa via a formula? I believe it does not work with a simple formula, so is there maybe a plugin for this?

Please, see test sheet Column B contains gregorian dates and formula in Column C and D converts it to Ethiopian. Range A13:B26 is support with Ethiopian month names.

Formulas in Column C and Column D are the same except second one doesn't use reference to date cell A2.

``````=
IF(
(B2-2812)-(QUOTIENT(B2-2812,366)*365+QUOTIENT(QUOTIENT(B2-2812,366),4))>=365,
MOD(29+MOD((B2-2812)-(QUOTIENT(B2-2812,366)*365+QUOTIENT(QUOTIENT(B2-2812,366),4)),30)+1-5-IF(MOD(QUOTIENT(B2-2812,365),4)=0,1,0),30)+1,
MOD((B2-2812)-(QUOTIENT(B2-2812,366)*365+QUOTIENT(QUOTIENT(B2-2812,366),4)),30)+1)
&" "&
VLOOKUP(
IF(
AND((B2-2812)-(QUOTIENT(B2-2812,366)*365+QUOTIENT(QUOTIENT(B2-2812,366),4))>359,
(B2-2812)-(QUOTIENT(B2-2812,366)*365+QUOTIENT(QUOTIENT(B2-2812,366),4))<365+IF(MOD(QUOTIENT(B2-2812,365),4)=0,1,0)),13,
IF((B2-2812)-(QUOTIENT(B2-2812,366)*365+QUOTIENT(QUOTIENT(B2-2812,366),4))>365+IF(MOD(QUOTIENT(B2-2812,365),4)=0,1,0),
MOD(
QUOTIENT((B2-2812)-(QUOTIENT(B2-2812,366)*365+QUOTIENT(QUOTIENT(B2-2812,366),4))-365-IF(MOD(QUOTIENT(B2-2812,365),4)=0,1,0),30),12)+1,

MOD(
QUOTIENT((B2-2812)-(QUOTIENT(B2-2812,366)*365+QUOTIENT(QUOTIENT(B2-2812,366),4)),30),12)+1)),\$A\$14:\$B\$26,2,0)
&" "&
1900+QUOTIENT(B2-2812-QUOTIENT(QUOTIENT(B2-2812,366),4),365)
``````

where B2 is cell with gregorian date.

General idea is to set starting point 01/01/1900 (1 Maskarram 1900) in Ethiopian or 12/09/1907 in gregorian. Then calculate date difference between starting point and required date and apply 30 day per month + 5 days 13th month rules and leap years.

and let me know if missed something.

• Cool, thanks ... You might want to copy the formular to the answer, so it does not get lost in case the test sheet get's lost?
– Alex
Dec 7, 2021 at 18:17

Based on the answer of Oleg_S, this is the formula:

First input the Gregorian date in cell `A3`

(This does not use written-out month names)

It was improved to stay blank, if the input cell is blank.

Use the formula in `B3`:

``````=IF(ISBLANK(A3),"",IF( (A3-2812)-(QUOTIENT(A3-2812,366)*365+QUOTIENT(QUOTIENT(A3-2812,366),4))>=365, MOD(29+MOD((A3-2812)-(QUOTIENT(A3-2812,366)*365+QUOTIENT(QUOTIENT(A3-2812,366),4)),30)+1-5-IF(MOD(QUOTIENT(A3-2812,365),4)=0,1,0),30)+1, MOD((A3-2812)-(QUOTIENT(A3-2812,366)*365+QUOTIENT(QUOTIENT(A3-2812,366),4)),30)+1) &" "& IF( AND((A3-2812)-(QUOTIENT(A3-2812,366)*365+QUOTIENT(QUOTIENT(A3-2812,366),4))>359, (A3-2812)-(QUOTIENT(A3-2812,366)*365+QUOTIENT(QUOTIENT(A3-2812,366),4))<365+IF(MOD(QUOTIENT(A3-2812,365),4)=0,1,0)),13, IF((A3-2812)-(QUOTIENT(A3-2812,366)*365+QUOTIENT(QUOTIENT(A3-2812,366),4))>365+IF(MOD(QUOTIENT(A3-2812,365),4)=0,1,0), MOD( QUOTIENT((A3-2812)-(QUOTIENT(A3-2812,366)*365+QUOTIENT(QUOTIENT(A3-2812,366),4))-365-IF(MOD(QUOTIENT(A3-2812,365),4)=0,1,0),30),12)+1, MOD( QUOTIENT((A3-2812)-(QUOTIENT(A3-2812,366)*365+QUOTIENT(QUOTIENT(A3-2812,366),4)),30),12)+1)) &" "& 1900+QUOTIENT(A3-2812-QUOTIENT(QUOTIENT(A3-2812,366),4),365))
``````