Work Days Open error

Question

Good morning,

I am hoping you can help me. I recently notice these errors( yellow)

It has the same formula as C139 and E139.

Thank you,

Therence

Answer 1

The displayed value you see (16-Jan-00) is how the integer value 16 is displayed when you specify a date format. This is the value your formula generates (28-Jan - 12-Jan = 16). You need to force the format of the cells in question to something appropriate, such as general or number.

Answer 2

Dear @Therence Valdez,

Good day. Thank you for posting your question in the Microsoft Q&A forum!

I’d like to clarify that the formulas used in columns C and E (for example, =TODAY()-B139) are calculating correctly. The behavior you’re seeing such as values displaying as 16‑Jan‑00 or 10‑Mar‑00 is due to cell formatting, not a formula error.

When Excel returns a number of days, but the cell is formatted as a Date, Excel interprets that number as a date offset starting from January 1900. As a result, the numeric day counts are displayed as historical dates.

To resolve this, please:

1. Select the affected cells (or the entire column).
2. Change the format to Number (with 0 decimal places).

If I have misunderstood your concerns, please feel free to provide a more detailed description of the issue you are experiencing. It would be helpful if you could clarify what each column represents and click on the cells showing the warning so I can review the exact formula being used.

To assist you more effectively, I would also recommend sharing a sample Excel file. This will allow me to review the formulas directly and provide more accurate guidance.

Thank you for your valuable time and cooperation. I am looking forward to your response!