Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Changing Dates in Text to Numbers

    I would like vba to convert date text such as "January 2013" to the following style"2/1/2013"/
    Thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Not entirely clear what you want VBA to do.

    What is the 2 for in "2/1/2013"?
    Is that d/m/yyyy format???
    Do you want "January 2013" to be converted to 2nd Jan 2013, in "2/1/2013" format??

    zeddy

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry, Zeddy, it was a misprint. It should have been convert "January 2013" to "1/1/2013".
    Sorry.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    "January 2013" to "1/1/2013".
    J,
    If Cell A1 contains January 2013, place the formula =DATEVALUE(A1) into another cell and set the format to Date *3/14/2001. This will yield a value of 1/1/2013.
    Not very clear about what you are doing with the day of the month.

    HTH,
    Maud

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Maud. Any ideas on VBA that would do the same thing (and save several keystrokes)?
    Thanks,Jlkirk
    Again if the cell I want to change is in text "January 2013" (or "February 2013", etc.) I would like the vba to change it to the first day of the respective month.
    Thanks.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Try the following code:

    Code:
    Public Sub ConvertDate()
    Range("A1").Value = CDate(Range("A1").Value)
    End Sub
    Where Cell A1 = January 2013 and the cell formatted as text,

    HTH,
    Maud

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    A little more useful in that the following code will convert the selected cell. Will work on different date structures.

    Dates1.jpg Date2.jpg

    Code:
    Public Sub ConvertDate()
    ActiveCell.Value = CDate(ActiveCell.Value)
    End Sub
    Last edited by Maudibe; 2013-02-06 at 20:36. Reason: added images

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Maud,
    Thanks again. Will these work on a range of cells say A5:A78?

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    I believe this is what you are lookin for:

    Code:
    Public Sub ConvertDate()
    Dim x As Range
    Dim y As Range
    Set x = Range("A5", "A78")
    
    For Each y In x
    y.Value = CDate(y.Value)
    Next y
    End Sub
    HTH
    Maud

  10. #10
    New Lounger
    Join Date
    Mar 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have question about date. I want to add workday on my worksheet not include weekend. is it possible if cell D3 = 21/03/2013 and I add Friday in D1 I want to check if it's Friday, if true then E3+3, else=+1 . thanks.

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    me12,

    Welcome to the lounge.

    Cell D1 formula =IF(WEEKDAY(D3,1)=6,E3+3,E3+1)

    Weekday(D3,1) retrieves the day of the week as a number. The 1 indicates "Start with Sunday". Since the day of the week is a Thursday, the equation evaluates to 5 and the if statement evaluates to false. This gives cell D1 the value of E3+1. Cell D3 must be formatted as date. You may have to list the date in mm/dd/yyyy format (3/21/2013)

    Date.jpg

    HTH,
    Maud
    Last edited by Maudibe; 2013-03-28 at 00:40.

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    If you wish to only use weekdays, alter the formula to:

    =IF(AND(WEEKDAY(D3,1)>1,WEEKDAY(D3,1)<7),IF(WEEKDA Y(D3,1)=6,E3+3,E3+1),"")

    If the date is a Saturday or Sunday, D1 will be blank.

    Maud

  13. #13
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    To display the spelled weekday, use the following formula.

    Code:
    =TEXT(A1,"dddd")
    If cell A1 contains 03/28/2013, the cell containing the formula will report Thursday.

    To get three letter abbreviations (e. g., Thu), substitute "ddd" into the formula.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Posting Permissions

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