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

[kfschaefer]

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

[MarkLaf]

Old thread, but I'll post because this was the top result when I was looking for an answer to this, and I was a bit confused by the current responses.

First off, in the data model (DAX) to convert a serial number to date, just change the column to the date data type - no need to use functions.

For zero-handling, create a calculated column of date data type and use the following function:

 =IF([date serial number]=0,BLANK(),[date serial number])

Mixing different data types usually confuses DAX (i.e. you'll probably get an error) and should be avoided. For this reason, convert zeros to blanks, don't leave as zeros and mix with dates.

In the query editor (M), the basic formula snippet would be "if [date serial number] = 0 then null else Date.From([date serial number])" - the actual step would look like the following if you want to add a custom column:

= Table.AddColumn(#"Previous Step", "Corrected Dates", each if [date serial number] = 0 then null else Date.From([date serial number]))

Or if you want to just transform the serial column:

= Table.TransformColumns(#"Previous Step", {"date serial number", each if _ = 0 then null else Date.From(_)})

[Eric_Zhang]

---

@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.

[Greg_Deckler]

In DAX, the syntax is:

IF(true/falsetest,true statement, false statement)

So, something like:

IF(datevalue[Termed] = TRUE,DATEVALUE([termded]),0)