Concatenating but having small error

[Learning Slowley]

I am trying to concatenate date into from 10 columns into 3.
The code that I have to concatenate columns K to Q are as follows

=IF(ISBLANK(L576),TEXT(K576,"dd/mm/yy"),TEXT(K576,"DD/MM/YY")&" - "&TEXT(L576,"DD/MM/YY"))

=IF(ISBLANK(N576),TEXT(M576,"hhmm"),TEXT(M576,"hhmm")&" - "&TEXT(N576,"hhmm"))

=IF(ISBLANK(Q576),IF(ISBLANK(P576),O576,O576&": "&P576),O576&": "&P576&" ("&Q576&")")

but I am having a display issue.....

In H, I and J, the cells are taken from K to Q.

1) There is always an event title so that simply needs to start the event preview in column J
2) There are not always further details in column P. If there are further details, I need it to start with a semi colon, if not then it should be blank.
3) I need the preview to display the location in brackets if there is a location. If not, the location should be blank.

With my formula above, if there is no start date, I am getting a 0000 in the time preview
Also, if there is no detail in column P but a location is in column Q, I am getting a semi colon and then the location in brackets.

Can anyone please assist me in putting this right?

[TABLE="width: 500"]
<tbody>[TR]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]Date preview[/TD]
[TD]Time preview[/TD]
[TD]Event copy display preview[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]StartTime[/TD]
[TD]EndTime[/TD]
[TD]Event[/TD]
[TD]Details[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]07/01/17 - 07/01/17[/TD]
[TD]1130 - 1430[/TD]
[TD]Event title: These are the details (London)[/TD]
[TD]07/01/17[/TD]
[TD]07/01/17[/TD]
[TD]11:30[/TD]
[TD]14:30[/TD]
[TD]Event title[/TD]
[TD]These are the details[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]08/01/17[/TD]
[TD]0000[/TD]
[TD]Event title: (London)[/TD]
[TD]08/01/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Event title[/TD]
[TD][/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]09/01/17 - 09/01/17[/TD]
[TD]1930[/TD]
[TD]Event title[/TD]
[TD]09/01/17[/TD]
[TD]09/01/17[/TD]
[TD][/TD]
[TD][/TD]
[TD]Event title[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Re: Concatenating but having small error - can anyone help?

Welcome to the forum.

For col I:

=IF(N576="",IF(M576="","",TEXT(M576,"hhmm")),TEXT(M576,"hhmm")&" - "&TEXT(N576,"hhmm"))

for col J:

=O576&IF(ISBLANK(P576),"",": "&P576)&IF(ISBLANK(Q576),""," ("&Q576&")")

 

[Learning Slowley]

Re: Concatenating but having small error - can anyone help?

Rory, that is incredible! Thank you so much for your help.

Kind regards

Tina

 

[RoryA]

Re: Concatenating but having small error - can anyone help?

Glad to help.