Advanced Date Formatting

[blemley]

Hi guys, I have a rather unique question. I have searched the forms, but no luck. Hopefully you all can help.

I have a course schedule with start date in column A and end date in col B formatted as mm/dd/yyyy. I have written the following function to display them as a date range in column C:

=text(A1, "mmm dd")&" - "&text(B1, "mmm dd")

This outputs a the date as: Jan 12 - Jan 14.

What I would like to do is take this a step further. If the end date and the start date are in the same month, I only need to display the month in the start date. For example: Jan 12 - 14. However, if the end date is in the next month, I need to display both month. Example: Mar 30 - April 01.

Beyond that, is there a way that I can have all dates 01-09 display as "1, 2, 3, 4, 5, 6, 7, 8, 9" without the preceding zero? Example: Mar 30 - Apr 1.

Thanks in advance!

 

[Oaktree]

Welcome to the board.

Maybe:

=TEXT(A1, "mmm d")&TEXT(B1, IF(A1=B1,"",IF(MONTH(B1)=MONTH(A1)," - d"," - mmm d")))

Which will also just display Jan 12 if A1 and B1 were to be the same date.

 

[Jonmo1]

Welcome to the board..

1st question - seems more trouble than it's worth. But I think this would work

=text(A1, "mmm dd")&" - "&text(B1, IF(Month(B1)=Month(A1),"dd","mmm dd"))



2nd question, that's easy. Just use d instead of dd in your date format

=text(A1, "mmm dd")&" - "&text(B1, IF(Month(B1)=Month(A1),"dd","mmm dd"))
change to
=text(A1, "mmm d")&" - "&text(B1, IF(Month(B1)=Month(A1),"d","mmm d"))

Hope that helps..

 

[blemley]

@jonmo1 & @oaktree - Thanks for the help! That's exactly what I was looking for. Thanks.

 

[Canapone]

Ciao,

=IF(MONTH(A1)=MONTH(B1),TEXT(A1, "mmm d")&" - "& TEXT(B1,"d"),TEXT(A1, "mmm d")&" - "& TEXT(B1,"mmm d"))

Hope it helps you