dates to text

  • Thread starter Thread starter davegb
  • Start date Start date
D

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.
 
You need to concatenate two strings with an "&"

=text(F4,"mm-dd,yy")&" - "&text(F5,"mm-dd,yy")
 
You need to concatenate two strings with an "&"

=text(F4,"mm-dd,yy")&" - "&text(F5,"mm-dd,yy")
Click to expand...
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.
 
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"))
 
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
 
Back
Top