Explanation
The TEXT function takes numeric values and converts them to text values using the format you specify. In this example, we are using the format “mmm d” for both TEXT functions in E5. The results are joined with a hyphen using simple concatenation.
Note: the other examples in column E all use different text formats.
End date missing
If the end date is missing, the formula won’t work correctly because the hyphen will still be appended to the start date (e.g.“March 1 - “).
To handle this case, you can wrap the concatenation and second TEXT function inside IF like so:
=TEXT(date1,"mmm d")&IF(date2<>""," - "&TEXT(date2,"mmm d"),"")
This creates the full date range when both dates are present, but outputs only the start date when the end date is missing.
Start date missing
To handle a case where both dates are missing, you could nest another IF like this:
=IF(date1<>"",TEXT(date1,"mmmm d")&IF(date2<>""," - "&TEXT(date2,"mmm d"),""),"")
This formula simply returns an empty string (””) when date1 is not available.
Explanation
Working from the inside-out, the WEEKDAY function takes a date and returns a number between 1 and 7. With default settings, the number 1 corresponds to Sunday and the number 7 corresponds to Saturday.
The CHOOSE function simply maps numbers to values. The first argument is the number to map, and subsequent arguments represent associated values.
In this case, 7 values have been provided in the order required to work with WEEKDAY’s Sunday through Saturday scheme.
With a date from column B, WEEKDAY returns a number which is fed to the CHOOSE function. CHOOSE returns the value at that position in the list of abbreviations.
