perm change of date not just format

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
=TEXT(MONTH(G9),"#0")&"/"&TEXT(YEAR(G9),"0000")

That can be reduced to:

=TEXT(G9,"m/yyyy")
 
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
 
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
 
What's the advantage of =TEXT(MONTH(G9),"#0")&"/"&TEXT(YEAR(G9),"0000")
compared with =TEXT(G9,"m/yyyy"), Kevin?
 
Thank you I will try it shortly.

Kevin B said:
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.
Click to expand...
 
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
 
I want to extract day out of 1/31/2007 and have only 1/2007 left in the cell
after
Click to expand...

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
 
Back
Top