I want to extract day out of 1/31/2007 and have only 1/2007 left in the cell
after

The function used assumes the first date to be in A1, change the cell address
as needed.

Insert a blank column to the right of your dates as a helper column.

In the first row enter the following formula:

=TEXT(MONTH(G9),"#0")&"/"&TEXT(YEAR(G9),"0000")

Copy down the length of the column. Then copy the column of dates you've
just created with the formula. Move to cell A1 and do an EDIT/PASTE SPECIAL
and select values.

Your dates have been replaced with the values the formula produced. Delete
the helper column when done.
--
Kevin Backmann


"charlie" wrote:

I want to extract day out of 1/31/2007 and have only 1/2007 left in the cell
after

=TEXT(MONTH(G9),"#0")&"/"&TEXT(YEAR(G9),"0000")

That can be reduced to:

=TEXT(G9,"m/yyyy")


--
Biff
Microsoft Excel MVP


"Kevin B" wrote in message
...
The function used assumes the first date to be in A1, change the cell
address
as needed.

Insert a blank column to the right of your dates as a helper column.

In the first row enter the following formula:

=TEXT(MONTH(G9),"#0")&"/"&TEXT(YEAR(G9),"0000")

Copy down the length of the column. Then copy the column of dates you've
just created with the formula. Move to cell A1 and do an EDIT/PASTE
SPECIAL
and select values.

Your dates have been replaced with the values the formula produced.
Delete
the helper column when done.
--
Kevin Backmann


"charlie" wrote:

I want to extract day out of 1/31/2007 and have only 1/2007 left in the
cell
after

Are you saying that you want text instead of the date, or do you want the
date to be changed to 1/1/2007 formatted as 1/2007?
=TEXT(A1,"m/yyyy")
=DATE(YEAR(A1),MONTH(A1),1) and format as m/yyyy
--
David Biddulph

"charlie" wrote in message
...
I want to extract day out of 1/31/2007 and have only 1/2007 left in the
cell
after

Excel stores dates as numbers. The dates start with 1/1/1900 which is day 1.
1/2/1900 is day 2. The date 1/13/2007 is actually stored as the number
39095. There is no "day" in the number 39005. Excel calculates the day
based on the number 39095. Do you want the text "1/2007" to be the result in
the cell?

Dave



"charlie" wrote in message
...
I want to extract day out of 1/31/2007 and have only 1/2007 left in the
cell
after

What's the advantage of =TEXT(MONTH(G9),"#0")&"/"&TEXT(YEAR(G9),"0000")
compared with =TEXT(G9,"m/yyyy"), Kevin?
--
David Biddulph

"Kevin B" wrote in message
...
The function used assumes the first date to be in A1, change the cell
address
as needed.

Insert a blank column to the right of your dates as a helper column.

In the first row enter the following formula:

=TEXT(MONTH(G9),"#0")&"/"&TEXT(YEAR(G9),"0000")

Copy down the length of the column. Then copy the column of dates you've
just created with the formula. Move to cell A1 and do an EDIT/PASTE
SPECIAL
and select values.

Your dates have been replaced with the values the formula produced.
Delete
the helper column when done.
--
Kevin Backmann


"charlie" wrote:

I want to extract day out of 1/31/2007 and have only 1/2007 left in the
cell
after

I would like to have just the month and year left when I'm done

"Wondering" wrote:

Excel stores dates as numbers. The dates start with 1/1/1900 which is day 1.
1/2/1900 is day 2. The date 1/13/2007 is actually stored as the number
39095. There is no "day" in the number 39005. Excel calculates the day
based on the number 39095. Do you want the text "1/2007" to be the result in
the cell?

Dave



"charlie" wrote in message
...
I want to extract day out of 1/31/2007 and have only 1/2007 left in the
cell
after

Thank you I will try it shortly.

"Kevin B" wrote:

The function used assumes the first date to be in A1, change the cell address
as needed.

Insert a blank column to the right of your dates as a helper column.

In the first row enter the following formula:

=TEXT(MONTH(G9),"#0")&"/"&TEXT(YEAR(G9),"0000")

Copy down the length of the column. Then copy the column of dates you've
just created with the formula. Move to cell A1 and do an EDIT/PASTE SPECIAL
and select values.

Your dates have been replaced with the values the formula produced. Delete
the helper column when done.
--
Kevin Backmann


"charlie" wrote:

I want to extract day out of 1/31/2007 and have only 1/2007 left in the cell
after

Select the cells you want to update and run:

Sub day_killer()
For Each r In Selection
With r
s = Split(.Text, "/")
.NumberFormat = "@"
.Value = s(0) & "/" & s(2)
End With
Next
End Sub

--
Gary''s Student - gsnu200747


"charlie" wrote:

I would like to have just the month and year left when I'm done

"Wondering" wrote:

Excel stores dates as numbers. The dates start with 1/1/1900 which is day 1.
1/2/1900 is day 2. The date 1/13/2007 is actually stored as the number
39095. There is no "day" in the number 39005. Excel calculates the day
based on the number 39095. Do you want the text "1/2007" to be the result in
the cell?

Dave



"charlie" wrote in message
...
I want to extract day out of 1/31/2007 and have only 1/2007 left in the
cell
after

On Thu, 27 Sep 2007 13:51:01 -0700, charlie
wrote:

I want to extract day out of 1/31/2007 and have only 1/2007 left in the cell
after

You can only do that if you want to have the cell contain TEXT and not a true
Excel date that you can subsequently manipulate as a date.

Why is the content vs the appearance so important? Depending on the reason,
there might be a 'better' solution.
--ron

As text?

"charlie" wrote in message
...
I would like to have just the month and year left when I'm done

"Wondering" wrote:

Excel stores dates as numbers. The dates start with 1/1/1900 which is day
1.
1/2/1900 is day 2. The date 1/13/2007 is actually stored as the number
39095. There is no "day" in the number 39005. Excel calculates the day
based on the number 39095. Do you want the text "1/2007" to be the result
in
the cell?

Dave



"charlie" wrote in message
...
I want to extract day out of 1/31/2007 and have only 1/2007 left in the
cell
after