INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel 2013-Display Date in MsgBox

Excel 2013-Display Date in MsgBox

(OP)
I have a command button where I input a well name in an input box. It returns the record in a msgbox. How do I get the date to display as a short date instead of a number.
This is the code I'm using:

Sub LOOKUP()
On Error GoTo MyErrorHandler:
Dim PRIMARY_WELLCOMP_SHORT_NAME As String

PRIMARY_WELLCOMP_SHORT_NAME = InputBox("Enter the Well Name :")
Det = "PRIMARY_WELLCOMP_SHORT_NAME : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 1, False)
Det = Det & vbNewLine & "WELL ANALYST : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 2, False)
Det = Det & vbNewLine & "OIL_RATE : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 3, False)
Det = Det & vbNewLine & "WATER_RATE : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 4, False)
Det = Det & vbNewLine & "GAS_RATE : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 5, False)
Det = Det & vbNewLine & "WELL_TEST_DATE : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 6, False)
Det = Det & vbNewLine & "TEST_FACILITY : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 7, False)
Det = Det & vbNewLine & "BATTERY_NAME : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 8, False)

MsgBox "Well Details : " & vbNewLine & Det

Exit Sub

MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Well Not Listed in the table."
ElseIf Err.Number = 13 Then
MsgBox "You have entered an invalid value."
End If

End Sub

This is the message box that gets returned:


Thank you in advance!

Dan Rogotzke

RE: Excel 2013-Display Date in MsgBox

Date is coded as number, convert value to text:
Det = Det & vbNewLine & "WELL_TEST_DATE : " & Format(Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 6, False), "dd/mm/yyyy")

combo

RE: Excel 2013-Display Date in MsgBox

... or:

CODE

Dim dat As Long

dat = Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 6, False)
Det = Det & vbNewLine & "WELL_TEST_DATE : " & CDate(dat) 

BTW - VBA questions (for Access) have better Forums here on TT smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel 2013-Display Date in MsgBox

(OP)
combo: ERROR. This is what I get:



Andrzejek: Data missing. This is what I get:

Dan Rogotzke

RE: Excel 2013-Display Date in MsgBox

OK, try this:

CODE

Dim dat As Long

dat = Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 6, False)
Debug.Print dat
Det = Det & vbNewLine & "WELL_TEST_DATE : " & CDate(dat) 

and report back what the Debug line shows.

From your original post, you had a value of 42984:

CODE

Dim dat As Long
dat = 42984
Debug.Print CDate(dat) 

Immediate Window shows: 9/6/2017

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel 2013-Display Date in MsgBox

This was replacement of the line of code with date, did you pasted as one line of code?

combo

RE: Excel 2013-Display Date in MsgBox

(OP)
combo: I'LL BE DANGED! HAHA! I failed to see the "Format" you put in the string. When I copy/pasted the line IT WORKED. Thank you sooooo much!

Dan Rogotzke

RE: Excel 2013-Display Date in MsgBox

(OP)
combo and Andrzejek,

I gotta tell ya, I'm an old fart now and I don't code near as much as I did years ago. And I've never coded in Excel before but I'm a little embarrassed to realize how simple the solution is. I don't know why I couldn't see it myself. I tried everything I could think of "except the correct thing". <Laughing loudly now>. I've forgotten way more than I remember these days. Thank you both for your help.

Dan

Dan Rogotzke

RE: Excel 2013-Display Date in MsgBox

2 points,
Don't forget to give combo a star (click on Great Post! link in his post)

And the reason you see a number instead of Date is because Excel (and data bases) treat/keep dates as numbers. The numbers is just formatted to show you as a Date. You may try it in Excel: in a cell type today's date: 11/3/2017 and you see a date. Now, change the cell format to General and you see 43042
That's how many days passed since 1/1/1900 (I think) smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close