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