Why am I getting error unwrapping Optional if it has a value?

VALUE! to return as "EARLY" or an empty cell?

• I've a TIME cell formula in excel to calculate total hours/minutes for latecomers and the formula is =SUMPRODUCT(--TEXT(A1-B1,"00\:00")) The value for A1 is 0800 and B1 is 0900 and it returns value as 01:00 after formulated which is good and that's what i wanted. However, if B1 value is 0730 (which means 30 minutes earlier than scheduled, I'm getting a #Value! error. I'll appreciate if someone could provide me with a solution as to return value as "EARLY" (if B1 value is earlier that A1) or an empty cell value (if B1 value is not entered). Any feedback pertaining to the above will be much appreciated. Thanks.

• Answer:

  Your formula does not always return the correct value. When A1 is 0900 and B1 is 0730, the first operation is subtaction: 0900 - 0730, which returns 170. The TEXT function then converts this to time as 1:70. A formula that works is =TEXT(A1,"00\:00")-TEXT(B1,"00\:00") To add the EARLY: =IFERROR(TEXT(A1,"00\:00")-TEXT(B1, "00\:00"),"EARLY") The above works for Excel 2007 and later. For earlier versions: =IF((B1+0)>(A1+0),"EARLY", TEXT(A1,"00\:00")-TEXT(B1, "00\:00")) EDIT: your example formula appears to be different than the text in your question. Switch the A1's and B1's in my formulas if needed.