Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Changing Number Formats


  • Please log in to reply

#1
swicklund

swicklund

    Member

  • Member
  • PipPip
  • 25 posts
I have been given a data table with the date format of [1yymmdd]. That is a 1 followed by the two digit year, two digit month, & two digit day. For example, 8/3/06 is given as 1060803. I have used a query & Left/Right to pull out the Month, Day, & Year pieces, but I am not able to put the pieces back to a usable date.

Can anyone help??? :whistling:
  • 0

Advertisements


#2
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
if you can get the different date portions (which is hard to get the center numbers out isn't it)...then you can use the =concatenate function to mix the results...i'm playing with it now..and having funky issues
  • 0

#3
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
AHA! found it...you can actually use the MID function to do this all from the original number

Syntax

MID(text,start_num,num_chars)

text = sample data or cell
start_num = the number of the character you want to start at (i.e. the 2nd character in the string to get the 06 out of the number)
num_chars = amount of characters to extract (i.e. the two digit number for the date)


GOT IT!!

assuming your original number (1060803) is in cell A1, this formula would put the proper date (030806) in b1

=RIGHT(A1,2)&MID(A1,4,2)&MID(A1,2,2)

you could technically do this all with the mid command by making it

=MID(A1,6,2)&MID(A1,4,2)&MID(A1,2,2)
  • 0

#4
swicklund

swicklund

    Member

  • Topic Starter
  • Member
  • PipPip
  • 25 posts
dsenette,

Thank you for your help!! I used your formula, with a slight modification to get me what I needed.

= CDate(Mid([date],4,2) & "/" & Mid([date],6,2) & "/" & Mid([date],2,2))

This not only put the numbers in the correct order, but also set it to DATE formatting.

Thank you again for your help!!!! :whistling:
  • 0

#5
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
yeah...hehe didn't think about the slashes... and the date-amajigs
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP