cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kfschaefer Member
Member

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

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

3 REPLIES 3
Super User
Super User

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

In DAX, the syntax is:

 

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

 

So, something like:

 

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
Moderator Eric_Zhang
Moderator

Re: 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")))

 

 Capture.PNG

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

MarkLaf Member
Member

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

 

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(_)})