How do I convert date serial to date and also handle zero values

---

kfschaefer wrote:

How do I convert a date serial value to a date, but also need to handle zero value .

 

if (datevalue(Termed) = true then Datevalue(termded) else 0

 

Pleae help with the correct syntax.

 

Karen

---

Hi Karen, if the date serial value in your post means , for example, Jan 1, 1900 is serial number 1 and Jan 2，1900 is serial number 2 etc, you can add a column with below expression.

DT = IF(Sheet1[DATE SERIAL NUMBER]=0,"0",IF(Sheet1[DATE SERIAL NUMBER]>=32767,FORMAT(DATE(2000,1,Sheet1[DATE SERIAL NUMBER]-36523),"YYYY-MM-DD"),FORMAT(DATE(1900,1,Sheet1[DATE SERIAL NUMBER]),"YYYY-MM-DD")))

• Why "-36523"
  Based on my test, regarding DATE(YEAR,MONTH,DAY), the parameter DAY is a 2 bytes interger, any value bigger than 32767 would be truncated as 32767. Since the serial number has been over 40000 recent years, I am using '2000-01-01'(36524 days since 1900-01-01) as the baseline.

If you have any question, feel free to let me know.