Dates: Change format of start date and end date to a range (e.g., Mar 22-Apr 1 2017)

[Alan in NJ]

Each week I have a spreadsheet in which column A has the start date and column B has the end date of an event, unless it is a one-day event in which case column B is either blank or has the same date as column A. I needed to create a new text column that contains the date reformatted into one of the following four forms: Dec 26 2017-Jan 9 2018 (different year), Mar 29-Apr 10 2017 (different month, same year), Mar 20-22 2017 (same month), Mar 21 2017 (one-day event).

I have handled this by adding six "helper" columns to the spreadsheet and separating out the following using the TEXT function: start date: mmm (col. C), d (col. D), yyyy (col. E) and end date: mmm (col. F), d (col.G), yyyy (col. H) and then using the following formula in a seventh column to populate it with the date in the new format:=IF(OR(B2="",B2=A2),C2&" "&D2&" "&E2,IF(E2<>H2,C2&" "&D2&" "&E2&"-"&F2&" "&G2&" "&H2,IF(C2<>F2,C2&" "&D2&" "&E2&"-"&F2&" "&G2&" "&H2,C2&" "&D2&"-"&" "&G2&" "&H2))).

This works fine, but it would be better if I had a macro that would allow me to highlight the two columns with the start date and end date and then have the adjacent (empty) column to the right populated with the newly formatted date. I envision either a temporary array or simply temporarily adding the same helper columns I populate with my functions above and then deleting them before the macro terminates.

I searched previous "date" posts but didn't find anything on point. Any assistance would be appreciated. Hopefully, my legwork makes this relatively easy for someone familiar with VBA.

 

[sheetspread]

You can do this with an if statement in a single cell, no helper columns or VBA needed.

 

[sheetspread]

Excel 2010
	A	B	C
3	12/26/2017	1/9/2018	Dec 26 2017-Jan 9 2018
4	3/29/2017	4/10/2017	Mar 29-Apr 10 2017
5	3/20/2017	3/22/2017	Mar 20-22 2017
6	3/21/2017	3/21/2017	Mar 21 2017
7	5/19/2017		May 19 2017
Sheet43 (2)

Cell Formulas
Range	Formula
C3	=IF(OR(A3=B3,B3=""),TEXT(A3,"mmm d yyyy"),IF(YEAR(A3)<>YEAR(B3),TEXT(A3,"MMM D YYYY")&"-"&TEXT(B3,"MMM D YYYY"),IF(MONTH(A3)<>MONTH(B3),TEXT(A3,"MMM D")&"-"&TEXT(B3,"MMM D YYYY"),TEXT(A3,"MMM D")&"-"&TEXT(B3,"D YYYY"))))

 

[Alan in NJ]

Thank you.