How Do I extract a middle number(s) within a string of numbers? 1

[wec43wec]

How do I find the single or double digit "day" number within a date format.

Example # 1 1/2/2008
and/or
Example # 2 1/12/2008

In example # 1, I am lookong to retrieve the number "2"

In example # 2, I am lookiing to retrieve the number "12"

I currently use a "right" / "left" function to retrieve the year and the month, however I am not sure how I would address a double digit month, but that is a few months down the road.

Can anyone provide a suggestion?

Thanks

 

[straybullet]

well, in Excel, it would be =DAY(A1) where A1 is the cell holding the date.

btw, you can also use =MONTH(A1) and =YEAR(A1) to get those values.

Let them hate - so long as they fear... Lucius Accius

 

[wec43wec]

Thanks Straybullet !

Note: Second question. I my previous example I have another question. How do I get the following

1/1/2008 to look like this 01/01/2008 ?

When I do: =year(A1)&Month(A1)&day(A1), I get 200811

 

[mintjulep]

faq68-5827

 

[ThereUare]

Ok, you need to use the DatePart Function. You can look it up in the help but here is an example to your examples.

In Access this formula will work for either example you gave.

Ex1: DatePart("d",#1/2/2008#)
Ex1: DatePart("d",[FieldName])

The "d" extracts the day only. You can also use it to extract the month by using "m" or the year by using "yy" or "yyyy".
If you look in the help it will show you all the ones you can use. Hope this helps.

 

[wec43wec]

I should have indicated that this problem was an Excel issue.

My major concernn is writing a formula to extract
date-data from a "fixed" position, however in a date format the position (numbers) within a date will change when looking at the 9th day vs the 10th day. I am aware of using the "left" / "right" function for month and year, but my difficulty is extractiing the "day".

Any other help would be much appreciative.

Thanks !

 

[Fenrirshowl]

wec43wec

In Excel you should use DateValue on your string - this will convert the text of 1/1/06 in a cell to 38718. (The cell holding 38718 can then be formatted as mm/dd/yyyy.)

Once the DateValue function is set up you can use the Day() and Month() functions (on the 38718 in my example) to extract the required information.

Fen

 

[AnotherHiggins]

straybullet's suggestion of using DAY will work regardless of date. There is absolutely no need for LEFT, RIGHT or MID unless your dates are stored as text. Here's a test to see if that's the case: Reformat your date cell. Does it change? If so, you have a date. If not, you should change it to a date.

Seriously, please take time to read and understand FAQ68-5827

Here's the short version: Dates are stored as the number of days since 1/1/1900. Today is 39,513 because it has been that many days since 1/1/1900. Times are stored as decimals based on the percentage of a 24-hr day. Noon is 0.5, 6 PM is 0.75, 10:37 PM is 0.942361111.

So if you have an actual date in a cell (as opposed to TEXT that is set up to look like a date - refer to first paragraph of this post), it is dead-easy to pull out whatever you want.

Similarly, once you understand that Excel is holding your date as a number (like 39513), you'll understand that you can make Excel DISPLAY it any way you want. "dd/mm/yyyy", "d/m/yy", "yyyy-mm-dd", "ddd", "mmmm d, yyyy", etc. are all just a matter of formatting the cell.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.