Display dates from multiple cells in one cell [Closed]

Report
-
Hi,

I want to combine dates from multiple cells into a single cell in the same row, with the result displayed in the format:

dd/mmmm/yyyyy, dd/mmmm/yyyy, etc. [i.e. individual dates to be separated by a comma].

Importantly, I want blank cells to be excluded from the result.

A couple of points for clarity:

- the dates in the origin cells will be selected by users from a data validation/drop down list.

- ideally the solution will update the result automatically if/when dates in the range are changed and/or if dates are added or deleted from the range (through insertion of a new column etc.)

The formulae I've tried to date (pardon the pun) without success:

1) =TEXT(A2,"dd mmmm yyy")&" - " &TEXT(B2,"dd mmmm yyyy") etc.

Issue: returns 00 January 1900 for blank cells and formula is not automatically updated upon insertion of a new column etc.

2) =CONCATENATE(TEXT(A2,"dd mmmm YYYY")," - ",TEXT(B2,"dd mmmm YYYY")) etc.

Issue: returns 00/January/1900 for blank cells and formula is not automatically updated upon insertion of a new column etc.

3) Marco: unfortunately I don't believe writing a code will work, as most users in the organization don't enable marcos, but if anyone knows how to push this out to users irrespective of their personal settings then I'd love to hear how and what code you'd suggest

Any help you're able to provide would be really appreciated.

Thanks in advance.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!