Grammatically correct Arabic hours and minutes in Excel

I wanted to be able to take the difference between two times in Excel (extra time worked vs. time missed) and write a grammatically correct sentence indicating how many hours and/or minutes difference there was (either under or over).

Even in English, I dislike seeing interfaces that ignore singular or plural units (e.g. You have 0 hours and 1 minutes remaining). In Arabic, the units take either singular, dual, or plural tenses for 1,2, and 3-10 respectively, and then revert to a singular tense for numbers 11 and above.

In this example, cell D2 contains time missed, cell D3 contains extra time worked, and cell D4 contains the difference between the two times. Since negative time doesn’t work in Excel, we can compare the results of subtracting each time from the other and then keep the larger of the two results =MAX(D2-D3,D3-D2); assuming valid times are entered, this will always result in a positive time.

In another cell, we can then put together some IF statements and glue the results into a sentence using CONCATENATE:

=IFERROR(CONCATENATE("لديك ", IF($D$2<$D$3,"", "نقص "), IF(NUMBERVALUE(TEXT($D$4, "[h]"))<1,"", IF(NUMBERVALUE(TEXT($D$4, "[h]"))=1, "ساعة", IF(NUMBERVALUE(TEXT($D$4, "[h]"))=2,"ساعتين",IF(AND(NUMBERVALUE(TEXT($D$4, "[h]"))>=3,NUMBERVALUE(TEXT($D$4, "[h]"))<=10),CONCATENATE(NUMBERVALUE(TEXT($D$4, "[h]")), " ", "ساعات"),IF(NUMBERVALUE(TEXT($D$4, "[h]"))>10,CONCATENATE(NUMBERVALUE(TEXT($D$4, "[h]")), " ", "ساعة"),""))))), IF(AND(NUMBERVALUE(TEXT($D$4, "[h]")),MINUTE($D$4))," و",""), IF(MINUTE($D$4)<1,"", IF(MINUTE($D$4)=1, "دقيقة واحدة", IF(MINUTE($D$4)=2,"دقيقتين",IF(AND(MINUTE($D$4)>=3,MINUTE($D$4)<=10),CONCATENATE(MINUTE($D$4), " ", "دقائق"),IF(MINUTE($D$4)>10,CONCATENATE(MINUTE($D$4), " ", "دقيقة"),""))))), IF($D$2<$D$3," زيادة ", "")),"ليس لديك ساعات")

If there are no hours, just minutes will display. If there are no minutes, just hours will display. If there are hours and minutes, they will both display with a linking “and”.

In order to support more than 24 hours, you can also replace HOUR(D4) with NUMBERVALUE(TEXT(D4, "[h]")).

Here’s an example with a deficit of 22 hours and two minutes: