month function

**vancoservices** · 06-30-2010, 02:14 PM

Hello everyone,

Here is my issue and I'm sure the answer is pretty simple but it's driving me crazy. I have a date in cell A7 (6/30/2010 for example) and in cell A8 I want to return the month of that specific date (Jun) so I use the formula =month(A7) and it returns a value of 6. That makes sense since June is the 6th month but when I change the format under "custom" to "mmm" in hopes of getting Jun, it always returns Jan. If I mess around with some other date formats it looks like the 6 is being read as 1/6/1900 and that's why I keep getting Jan instead of Jun. Is there a way to get it to return Jun as the month of that specific date instead of Jan? Much thanks to anyone who can help me out.

**shg** · 06-30-2010, 02:15 PM

The number 6 is Jan 6, 1900.

=A7 and format as "mmm"

**vancoservices** · 06-30-2010, 02:24 PM

Simple enough, but is that cell going to work properly since it is only formatted differently because I will be doing vlookups, countifs, and other things with it?

**shg** · 06-30-2010, 02:25 PM

I might be able to answer that if I knew what you were doing ...

**vancoservices** · 06-30-2010, 02:29 PM

Well one thing I will be doing is counting the number of records in a separate sheet that occurred during that specific month so I want to use that cell as a reference in the equation. Does that help?

**shg** · 06-30-2010, 02:35 PM

For that you need the actual date, and, for example, a COUNTIFS formula:

=COUNTIFS(A1:A24, ">=01 Jun 2010", A1:A24, "<=30 Jun 2010")

**vancoservices** · 06-30-2010, 02:40 PM

That's what I was trying to avoid because what I'm actually doing is a lot more complicated that a single countifs statement... Oh well I guess I will have to figure out some other way to do it. Thanks for the help.