Combine Multiple Date & Time groups into one Cell

[matthewajackson13]

I am trying to merge 4 date and time cells together into 1 cell. I am using the following formula:

=CONCATENATE(TEXT(Table_query__42[@[Soft Lock 1 End]],"mm/dd/yyyy h:mm AM/PM"), "; ",TEXT(Table_query__42[@[Soft Lock 1 Start]], "mm/dd/yyy h:mm AM/PM"), "; ",TEXT(Table_query__42[@[Soft Lock 2 End]], "mm/dd/yyy h:mm AM/PM"),"; ",TEXT(Table_query__42[@[Soft Lock 2 Start]], "mm/dd/yyyy h:mm AM/PM"))

It is working to combine all of the dates however, the blank fields are showing up as 01/00/1900 12:00 AM.

I would like the blank fields to show up blank.

Is there a way to combine 4 date and time cells together and exclude blank cells? I have tried to combine the cells without the formatting however, it brings them together as just numbers and not dates.

Any help would be great. Some example data set below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]09/25/2019 8:00 AM[/TD]
[TD]9/25/2019 12:00 PM[/TD]
[TD]10/01/2019 8:00 AM[/TD]
[TD]10/01/2019 12:00 PM[/TD]
[/TR]
[TR]
[TD]9/25/2019 8:00 AM

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/25/2019 8:00 AM[/TD]
[TD]9/25/2019 12:00 PM

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, welcome to the board.

Perhaps something like replacing
TEXT(Table_query__42[@[Soft Lock 1 Start]], "mm/dd/yyy h:mm AM/PM")

with
IF(Table_query__42[@[Soft Lock 1 Start]]=0,"",TEXT(Table_query__42[@[Soft Lock 1 Start]], "mm/dd/yyy h:mm AM/PM"))

 

[matthewajackson13]

Thanks!

I think I figured it out. I did a version of what you have above.

=CONCATENATE((IF(Table_query__42[@[Soft Lock 1 End]], TEXT(Table_query__42[@[Soft Lock 1 End]],"mm/dd/yyyy h:mm AM/PM"),"")),"; ",(IF(Table_query__42[@[Soft Lock 1 Start]],TEXT(Table_query__42[@[Soft Lock 1 Start]], "mm/dd/yyyy h:mm AM/PM"),"")),"; ",(IF(Table_query__42[@[Soft Lock 2 End]],TEXT(Table_query__42[@[Soft Lock 2 End]], "mm/dd/yyyy h:mm AM/PM"),"")),"; ",(IF(Table_query__42[@[Soft Lock 2 Start]],TEXT(Table_query__42[@[Soft Lock 2 Start]],"mm/dd/yyy h:mm AM/PM"),"")))

Thank you for your help!!!

 

[Gerald Higgins]

Hth, tftf