Solved

How do I construct a date in Excel VBA from a shortened month name ?

Posted on 2014-07-25
4
159 Views
Last Modified: 2014-07-25
Hi Experts,

I have an Excel sheet in which dates are split out across 3 columns. For example, I have 01 in column A, "Sep" in column B and 2013 in column C.

I want to create a formatted date (dd/mm/yyyy) in column D. How do I do this in VBA (I can do it manually entering a formula) ?

Thanks
Toco
0
Comment
Question by:Tocogroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40219163
which formula are you using?
0
 
LVL 13

Accepted Solution

by:
duncanb7 earned 500 total points
ID: 40219167
You can try this
Duncan
Sub test()
Sheets(1).Select
With ActiveSheet
.Range("D1").NumberFormat = "dd/mm/yyyy"
.Range("D1").Value = .Range("A1") &. Range("B1") & .Range("C1")
End With
End Sub

Open in new window

0
 

Author Closing Comment

by:Tocogroup
ID: 40219199
Many thanks. That works fine.

To answer the previous expert's question I used =DATEVALUE(1&"/"&MONTH(C2&1)&"/"&B2) as a formula within the sheet. No doubt this can be adapted to a VBA solution.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40219205
Thanks for your points

Have a nice day

Duncan
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question