+ Reply to Thread
Results 1 to 8 of 8

Convert a general format string to calendar date in Excel 2010 vba?

  1. #1
    Registered User
    Join Date
    06-14-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Talking Convert a general format string to calendar date in Excel 2010 vba?

    I have a general format string in an Excel 2010 spreadsheet cell that needs to be converted to a corresponding calendar date and placed in next column. Any ideas on a VBA subroutine that would pass the converted date to a value in the next column or I can plug into an email. Ex. Cell A1 has string "week 01" and needs to be converted to March 5, 2012 and placed in B1, cell A2 has string "Week 02" and needs to be converted to March 12, 2012 and placed in B2 (second week of the month), A3 has "week 05" and needs to be converted to April 2, 2012 (5th week from beginning date March 5) an placed in B3.
    Last edited by Kuriakos; 06-29-2012 at 07:38 PM. Reason: Resolved

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Convert a general format string to calendar date in Excel 2010 vba?

    Kuriakos,

    Welcome to the forum!

    Does it have to be a macro? You could get the same results with this formula in cell B1 and copied down (format cells as desired):
    Please Login or Register  to view this content.

    If it needs to be a macro though, this should work for you:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-14-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Convert a general format string to calendar date in Excel 2010 vba?

    Thanks for the welcome. Wow!! That was fast, however, the date returned in cell B2 and B3 are the same. If A1 = "Week 01" then A2 should be March 5, 2012, but if next row (B1) is "Week 07" then B2 should be April 23, 2012. Which is 7 weeks from March 5, 2012.

    I love it when you guru's show off.

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Convert a general format string to calendar date in Excel 2010 vba?

    Alright, I am confused because there is conflicting information:

    From your original post:
    Quote Originally Posted by Kuriakos View Post
    Ex. Cell A1 has string "week 01" and needs to be converted to March 5, 2012 and placed in B1, cell A2 has string "Week 02" and needs to be converted to March 12, 2012 and placed in B2 (second week of the month), A3 has "week 05" and needs to be converted to April 2, 2012 (5th week from beginning date March 5) an placed in B3.

    From your reply:
    Quote Originally Posted by Kuriakos View Post
    Thanks for the welcome. Wow!! That was fast, however, the date returned in cell B2 and B3 are the same. If A1 = "Week 01" then A2 should be March 5, 2012, but if next row (B1) is "Week 07" then B2 should be April 23, 2012. Which is 7 weeks from March 5, 2012.

    So in your original post, your data is setup like this:
    Please Login or Register  to view this content.

    And in your reply, the data is setup like this:
    Please Login or Register  to view this content.


    The information I posted earlier was going off of your original data setup. Does it instead need to go the other way?

  5. #5
    Registered User
    Join Date
    06-14-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Convert a general format string to calendar date in Excel 2010 vba?

    ...the original data setup was correct (my bad) and it's working fine. Sorry for the confusion. Strange thing is that as I entered data in Column A the formula carried to B down without have to copy the formula down. Should I test this with data already in Column A, then add the formula in column B?

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Convert a general format string to calendar date in Excel 2010 vba?

    Right, I would enter data in column A, and then input the formula and copy down. If the formula isn't pulling the correct results, make sure your workbook calculation is set to automatic:
    Office Button -> Excel Options -> Formulas -> Select "Automatic" -> OK

  7. #7
    Registered User
    Join Date
    06-14-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Convert a general format string to calendar date in Excel 2010 vba?

    Quote Originally Posted by tigeravatar View Post
    Right, I would enter data in column A, and then input the formula and copy down. If the formula isn't pulling the correct results, make sure your workbook calculation is set to automatic:
    Office Button -> Excel Options -> Formulas -> Select "Automatic" -> OK
    Thank you! That resolved the problem.

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Convert a general format string to calendar date in Excel 2010 vba?

    You're very welcome, I'm glad it's working for you now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1