0

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 2

1

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.

1
  • 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
0

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.