Replacing Part of a Cell's Content

  • Thread starter Thread starter mommy2kh
  • Start date Start date
M

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!
 
If I understand you correctly Instead of selecting number select Custom.
Paste this into the texbox - mmm-yy h:mm:ss AM/PM
 
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!
 
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!
Click to expand...

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