×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Text to Format as Date?

Text to Format as Date?

Text to Format as Date?

(OP)
There's a field with 6 characters in which the last 4 are the month and date (mmdd)
Looks like this for instance, today, the batch number is 000809

I need to convert that into a date so I've attempt the following:
Format(Right([Batch Number], 4), "mm/dd/" & "2018" in order to achieve 08/09/2018
However it is producing incorrect dates based on the Batch Number

So then I've tried in 2 steps
First Right([Batch Number],4) as InvoiceDate
Then Format([InvoiceDate], "mm/dd/" & "2018")

This produces the same incorrect dates.

Example: It should produce 01/07/2018

SalesOrderNo ImprintID InvoiceNo Batch Number InvoiceDate Expr1
A525894 93890372 A54490 00107 0107 04/16/2018

RE: Text to Format as Date?

You have provided 2 examples of Batch Number: 000809 and 0107

So try this:

CODE

Dim strBNo As String
Dim datMyDate As Date

strBNo = "000809"
datMyDate = CDate(Left(Right(strBNo, 4), 2) & "/" & Right(strBNo, 2) & "/2018") 

or

CODE

Dim strBNo As String
Dim datMyDate As Date

strBNo = "0107"
datMyDate = DateSerial("2018", Left(Right(strBNo, 4), 2), Right(strBNo, 2)) 

BTW - I am not crazy about hard-coding 2018. What will happen next year?

---- Andy

There is a great need for a sarcasm font.

RE: Text to Format as Date?

(OP)
Perfect! Thank you very much!

This works as hoping!

CODE

CDate(Left(Right(Batch Number, 4), 2) & "/" & Right(BatchNumber, 2) & "/2018") 

RE: Text to Format as Date?

You are welcome smile
Both of these examples should work for you. I would tend to use the DateSerial myself, but that's a personal preference.


---- Andy

There is a great need for a sarcasm font.

RE: Text to Format as Date?

I agree with Andy. The idea of hard coding the year is going to become problematic very quickly.

In the string originally provided, the last 4 digits are 2018. If that year will always correspond with the date of interest it would be a simple matter of using & Right(completestring,5) (including the /) in place of the & "/2018" Andy provided. If it does not correspond, perhaps you can use & "/" & Right(completestring,4) along with some calculation and either -1 or +1 as necessary to create the correct year.

Good luck.https://www.tek-tips.com/viewthread.cfm?qid=178873...

RE: Text to Format as Date?

(OP)
Thank you. Fortunately, this is a one-time task.

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!

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