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?
2 Answers
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.
Please, test different date and check with converter http://www.epistemeacademy.org/calendars/yearly_calendar.html?cyear=2020&vADBC=AD&CCode=Ethiopian&day=1
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?– AlexDec 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))