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

# Changing Number Formats

### #1 swicklund Posted 16 March 2007 - 10:54 AM

swicklund

Member

• Member
• 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???
• 0

### #2 dsenette Posted 16 March 2007 - 11:28 AM

dsenette

Je suis Napoléon!

• 26,023 posts
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 Posted 16 March 2007 - 11:45 AM

dsenette

Je suis Napoléon!

• 26,023 posts
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 Posted 16 March 2007 - 12:19 PM

swicklund

Member

• Topic Starter
• Member
• 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!!!!
• 0

### #5 dsenette Posted 16 March 2007 - 12:21 PM

dsenette

Je suis Napoléon!

• 26,023 posts
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