columns with mixed data types

[Paridhisharma]

HI BI Folks,

I have a column of data type TEXT and it has both Dates and Texts:

Due Date

10/31/2017

09/13/2017

TBD

10/20/2017

12/01/2017

TBD

I want to create a new column for Due Date Status. If Due date is after today then status should be "In Progress", if Due Date is after 2 days then status should be "Due in 48 hours", if Due Date is TBD then Status should be "TBD"

For Example:

Due Date               Due Date Status

10/31/2017            In Progress

09/13/2017            Complete 

TBD                        TBD

10/20/2017            Due in 48 Hours

12/01/2017            In Progress

TBD                        TBD

The Due Date Column is of type TEXT and it is not getting converted to Date. Can i create a new column of type DATE where if the value of Due Date is Date, then I get the Date otherwise Blank for TBD Value.

Any help would be appreciated. 

Regards,

Paridhi

[fhill · Accepted solution]

Create a 'Check Column' with 1900/01/01 for TBD.  Once created, now you can format this column as DATE format.

CheckColumn = IF(Table1[Due Date] = "TBD",DATE(1900,01,01),DATEVALUE(Table1[Due Date]))

Use this 2nd column to do the date checks and include logic to pull out the 1900/01/01.  ** Assuming you don't have any dates before 1899.

Text = IF(Table1[CheckColumn] > TODAY(), "In Progress", IF(Table1[CheckColumn] = Date(1900,01,01),"TBD","Complete"))

[Eric_Zhang · Accepted solution]

@Paridhisharma

You can also replace the "TBD" with a specific date, say "2099-01-01", then change the column type to Date in Query Editor.

[Paridhisharma]

HI Eric,

Thankyou for your response. 

But what if we have more text values coming in that column apart from TBD? Like Pending etc. 

ex:

Due Date

1/1/2017

TBD

1/2/2017

Pending

1/3/2017

As i am not sure of the different values we are going to get in that column, i want all the text values to be replaced by date 01/01/2099. is that possible?

[fhill · Accepted solution]

Create a 'Check Column' with 1900/01/01 for TBD.  Once created, now you can format this column as DATE format.

CheckColumn = IF(Table1[Due Date] = "TBD",DATE(1900,01,01),DATEVALUE(Table1[Due Date]))

Use this 2nd column to do the date checks and include logic to pull out the 1900/01/01.  ** Assuming you don't have any dates before 1899.

Text = IF(Table1[CheckColumn] > TODAY(), "In Progress", IF(Table1[CheckColumn] = Date(1900,01,01),"TBD","Complete"))