dates to text

[davegb]

I'm trying to figure out how to convert same dates I have into a text
string. I.e., in cells F4 and F5 there are dates resulting from
manipulation of other dates. If the 2 dates are June 1, 2005 and Aug 1
2006, I want a text field that looks like:
06-12,05 - 01-08,06.
It lists the remaining months in the year and the months up to the
second date. If there's a full intervening year, it should list that
as 01-12,06 between the other two. Is this doable w/o code? If I need
to write a program, I will.
Thanks for the help.

 

[Joel]

You need to concatenate two strings with an "&"

=text(F4,"mm-dd,yy")&" - "&text(F5,"mm-dd,yy")

 

[salgud]

You need to concatenate two strings with an "&"

=text(F4,"mm-dd,yy")&" - "&text(F5,"mm-dd,yy")

Thanks for your reply, it helps. But it's not quite what I'm asking for.
I'm beginning to think I'll need some code to create the string.

 

[Joel]

Try this. it is the old formula for less the 2 years different from F4 to
F5. I added a third date if there is a full year between the dates.

=IF(YEAR(F5)-YEAR(F4)<2,TEXT(F4,"mm-dd,yy")&" -
"&TEXT(F5,"mm-dd,yy"),TEXT(F4,"mm-dd,yy")&" -
"&TEXT(F4,"mm-dd,")&RIGHT(YEAR(F4)+1,2)&" - "&TEXT(F5,"mm-dd,yy"))

 

[Rick Rothstein \(MVP - VB\)]

I think this VB function will do what you want. Place it in a Module and you
can either call it from within your own subroutines and/or functions or
simply call it as a User Defined Function (UDF) from the worksheet itself.
Just pass in two dates (in any order) and it should return a text string
containing what you requested...

Function MakeDateRange(D1 As Date, D2 As Date) As String
Dim Y As Long
Dim StartDate As Date
Dim EndDate As Date
If D1 < D2 Then
StartDate = D1
EndDate = D2
Else
StartDate = D2
EndDate = D1
End If
If Year(StartDate) = Year(EndDate) Then
MakeDateRange = Format(StartDate, "mm") & "-" & _
Format(EndDate, "mm") & "," & Right(Year(EndDate), 2)
Else
MakeDateRange = Format(StartDate, "mm") & "-12," & _
Right(Year(StartDate), 2) & " - "
For Y = Year(StartDate) + 1 To Year(EndDate) - 1
MakeDateRange = MakeDateRange & "01-12," & Right(Y, 2) & " - "
Next
MakeDateRange = MakeDateRange & "01-" & Format(EndDate, "mm") & _
"," & Right(Year(EndDate), 2)
End If
End Function


Rick