Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Converting Date to Text

    Using Excel 2000 and VBA, I need to convert dates into text to display in a list box. For example, 8/1/2004 would be converted to "Aug 2004". When I try converting the date to text by formatting the cell, I get a number which is the Julian date.

    If this is not doable in a straightforward way, I could use something like MONTH($A1) and YEAR($A1), test the month number in VBA to see what month name will be assigned and then join the name and year together.

    My goal is to display sorted unique month/year values in a text box, but I only need advice on the date conversion process.

    Thanks for your help.

    Jerry

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by JerryDal
    When I try converting the date to text by formatting the cell, I get a number which is the Julian date.
    Excel is persistant about retaining date formatts Aug 2004 will be converted back to a date format like 8/1/2004 if Excel thinks your entering a date. When you convert the cell formatting to text it will show as a numeric date. You need to first convert to the text format then apply the date as Month Year string to the text formatted field. Try this in your code. With the Format statement It won't make a diference if the date format is ###### or mm/dd/yy excel will know its a date.

    Selection.NumberFormat = "@"
    Selection = Format(ActiveCell,"MMM YYYY")


    .
    ~

    Bill

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thanks for your reply to my Excel question. A column of dates if being read and then presented in a list of unique month/year values in this format: "Aug 2004", which will eventually produce a report of rows that fall within the month and year chosen from the list box.

    The VBA solution I came up with to do this conversion for display in a list box is to temporarily store formulas in cells to calculate the results I want.

    Formula to calculate the month name and join it to the year:
    =MID("JanFebMarAprMayJunJulAugSepOctNovDec",MONTH( $S1)*3-2,3)&" "&YEAR($S1)

    I put the values into an array and sort them for adding to a list of months with the year in a text box. When adding to the list box, if a value is a duplication of the previous item, it is not added to the list.

    The date chosen is temporarily stored in a cell as a date that conveniently is the first day of the month, which is the starting date for the report criteria. The last day of the month is then calculated by adding a month to this date (=MONTH($BW)+1) and subtracting 1 (subtracts a day). These two dates, the starting and ending dates, are the basis for a report.

    Thanks again.
    Jerry

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Jerry
    just a quick comment, if your using VBA anyway you can do this slightly neater
    to use a worksheet function for the date you can use
    =DATE(YEAR($S1),MONTH($S1),1) and then format this to month year
    or you can use DateSerial and store this to a variable you can also subtract 1 from the day and it will produce the end date as you have above with cells

    Here's an example of what i think your trying to do

    Code:
    Private Sub UserForm_Activate()
        Dim wksData As Worksheet
        Dim fdom() As Date
        Dim Lastrow As Long
        
        'set object variable
        Set wksData = Worksheets("Sheet1")
        Lastrow = wksData.Cells(Rows.Count, 1).End(xlUp).Row
        'reset array sizes to hold the data
        ReDim fdom(Lastrow) As Date
        ReDim ldom(Lastrow) As Date
        
        'fill the array with intial dates of the months in the first cell
        
        For i = 1 To Lastrow
            fdom(i) = DateSerial(Year(wksData.Cells(i, 1).Value), Month(wksData.Cells(i, 1).Value), 1)
        Next i
        
        'put array on worksheet
        For i = 1 To Lastrow
            wksData.Cells(i, 3).Value = Format(fdom(i), "mmm YYYY")
        Next i
        
        'put the dates onto the worksheet then filter them
        With Range(wksData.Cells(1, 3), wksData.Cells(Lastrow, 3))
            .Sort Cells(1, 3), xlAscending
            .AdvancedFilter xlFilterCopy, , Cells(1, 6), True
        End With
        
        'get the new number of rows to fill array with
        Lastrow = wksData.Cells(Rows.Count, 6).End(xlUp).Row
        'resize array to new size this also clears the array
        ReDim fdom(Lastrow) As Date
        
        For i = 1 To Lastrow
            'Fill when list is filtered if statement to remove inital value if applicable
            If Not (i = 1 And wksData.Cells(1, 6).Value = wksData.Cells(2, 6).Value) Then
                ListBox1.AddItem Format(wksData.Cells(i, 6).Value, "mmm yyyy")
            End If
        Next i
        
        'clear working of of worksheet
        wksData.Cells(1, 3).EntireColumn.Clear
        wksData.Cells(1, 6).EntireColumn.Clear
        
    
    End Sub

    HTH
    Dave
    Last edited by DavidCoutts; 10-22-04 at 11:07.

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thanks Dave. You have given me a lot of information to work with and I will put it to use in my Excel VBA solution to create a list of month and year items for display in a list box.

    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •