Replacing Part of a Cell's Content

[mommy2kh]

Is there a way to change part of a cell's content? I have the following data -

7/7/2008 1:25:00 PM
7/7/2008 1:18:00 PM
7/7/2008 6:19:00 PM
7/7/2008 12:34:00 AM

I want to change 7/7/2008 to Jul-07 but still keep the time the way it is.
I can change the way the data is viewed (using Format, Cell, Number) but it
doesn't change the actual data in the cell. If I do a Replace command it
replaces all the data and I lose the time information. I need to change the
date data because if not I cannot get a COUNT IF function to work on it. Any
ideas? Thanks!

 

[Mike]

If I understand you correctly Instead of selecting number select Custom.
Paste this into the texbox - mmm-yy h:mm:ss AM/PM

 

[mommy2kh]

Thanks for your reply. I have the Custom Format set up already but that only
changes the way the data is viewed, not the actual cell content. Is there a
way to change part of a cell's content? Thanks!

 

[Max]

One tinker ..

In B1, copied down:
=TEXT(A1,"'mmm-dd")&" "&TEXT(A1,"h:mm:ss AM/PM")

Then in C1:
=SUMPRODUCT(--ISNUMBER(SEARCH("Jul-07",B1:B100)))
to count
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik

 

[Ron Rosenfeld]

Is there a way to change part of a cell's content? I have the following data -

7/7/2008 1:25:00 PM
7/7/2008 1:18:00 PM
7/7/2008 6:19:00 PM
7/7/2008 12:34:00 AM

I want to change 7/7/2008 to Jul-07 but still keep the time the way it is.
I can change the way the data is viewed (using Format, Cell, Number) but it
doesn't change the actual data in the cell. If I do a Replace command it
replaces all the data and I lose the time information. I need to change the
date data because if not I cannot get a COUNT IF function to work on it. Any
ideas? Thanks!

If you want Excel to view the data as a date or date + time, you won't be able
to change the contents the way you want.

Since you know how to display it the way you want, and your problem is the
COUNTIF function, it seems to me it would be easier to work with that.

You didn't post what you wanted to do with the COUNTIF function, but, for
example, if you want to COUNT all entries that occurred on Jul-07 during any
year, you could use SUMPRODUCT:

=SUMPRODUCT((MONTH(rng)=7)*(DAY(rng)=7))
--ron

 

[daddylonglegs]

If you simply want to count entries in July-08 you could leave the data as is
and use

=SUMPRODUCT((MONTH(A1:A100)=7)*(YEAR(A1:A100)=2008))