Removing generic date value within formula

[Damian37]

Hello all,
I've written a formula that removes the time stamp and time zone for a date value. Wherever the date field is blank a generic date is placed there '01/00/1900'. I would like to replace the generic dates with a blank instead. The formula I'm using is:

=TEXT(IF(CELL("type",F230)="l",LEFT(F230,FIND(" ", F230)),INT(F230)),"mm/dd/yyyy")

I thank you for any help anyone can give me.
D.

 

[sandy666]

maybe check here: CELL function

"type"

Text value corresponding to the type of data in the cell. Returns "b" for blank if the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else.

 

[sandy666]

maybe try ...CELL("type",F230)=CHAR(108)...

 

[Damian37]

maybe try ...CELL("type",F230)=CHAR(108)...

Hi Sandy666,
The CHAR(108) addition did allow me to still see my dates, but the generic dates are still there.
D.

 

[sandy666]

post your date/dates

 

[Damian37]

post your date/dates

The generic date structure that is coming up for blank cells is '01/00/1900'. I cannot seem to get these cells to stay blank so when I use them in a calculation formula that I have, I don't receive #VALUE instead of just a blank cell.
D.

 

[sandy666]

try =IF(ISBLANK(P13),"",TEXT(IF(CELL("type",P13)=CHAR(108),LEFT(P13,FIND(" ", P13)),INT(P13)),"mm/dd/yyyy"))
adjust reference

 

[Damian37]

post your date/dates

I'm not sure I understand your request. The dates are regular short dates, that have been converted from dates with a timestamp and time zone within them. The formula removes the timestamp and time zone, however for blank cells, it creates a generic date '01/00/1900' instead of returning a blank value. I would just like to have the cell appear blank instead of having a generic date placed there.

 

[Damian37]

try =IF(ISBLANK(P13),"",TEXT(IF(CELL("type",P13)=CHAR(108),LEFT(P13,FIND(" ", P13)),INT(P13)),"mm/dd/yyyy"))
adjust reference

This formula worked! Thank you so much Sandy666!
D.

 

[sandy666]

You are welcome
Have a nice day

btw.
with PowerQuery this is simple:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Raw data[/td][td][/td][td=bgcolor:#70AD47]Parse[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]2019-05-23T20:09:37.8739799+02:00[/td][td][/td][td=bgcolor:#E2EFDA]

23/05/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] [/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Raw data", type text}}),
    Parse = Table.AddColumn(Type, "Parse", [COLOR="#0000FF"]each Date.From(DateTimeZone.From([Raw data])), type date[/COLOR]),
    ROC = Table.SelectColumns(Parse,{"Parse"})
in
    ROC[/SIZE]