ExcelBanter - Help with SumIf

Thanks for confirming this, Biff. (Puts my mind at rest on that little
issue !)

Pete

Biff wrote:

Perhaps Biff can test this
out by entering Nov-05 in a cell pre-set to mmm-yy - if my reasoning is
correct this should display as Nov-06 but the underlying value will be 5th
Nov 2006.

Your reasoning is correct (as usual!)

Biff

"Roger Govier" wrote in message
...
Good reasoning, Pete and my bet would be on you being correct.

--
Regards

Roger Govier

"Pete_UK" wrote in message
ps.com...
I think what is happening is that in the UK we would enter dates as
dd/mm/yy, so if you enter 5/11 in a cell then Excel assumes this is a
date and will assume that the year is missing and thus take it as being
05/11/2006.

In the US and Canada, however, dates are normally entered as mm/dd/yy,
so if you enter Nov-06 Excel will again assume the year is missing and
treat this as 06/11/2006 (or 11/06/2006). Perhaps Biff can test this
out by entering Nov-05 in a cell pre-set to mmm-yy - if my reasoning is
correct this should display as Nov-06 but the underlying value will be
5th Nov 2006.

I also don't like changing the Regional Settings <bg

Hope this helps.

Pete

Sandy Mann wrote:

Hi Biff,

I never doubted you Biff. I tried changing the Regional Settings to
English
(United States) and got the same as you.

I usually don't like changing the Regional Settings because as my mother
told me sixty years ago about pulling faces, "If the wind changes you'll
be
stuck like that!" and although never believed her even then; you never
know.
<g
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

with @tiscali.co.uk

"Biff" wrote in message
...
Screencap:

http://img294.imageshack.us/img294/6...eformatnu3.jpg

Biff

"Sandy Mann" wrote in message
...
Keep going lads, we've got him outnumbered. <g

--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

with @tiscali.co.uk

"Pete_UK" wrote in message
ups.com...
Hi Roger, Sandy, Biff,

just to complete the range, with XL 2000 I also get 01/11/2006 when
I
enter Nov-06. Must be something to do with Regional Settings.

Pete

Roger Govier wrote:

Sandy

XL2003
If I type Nov-06 into a standard cell or a cell preformatted as
mmm-yy
I
also see
01/11/2006

What a difference a stretch of water can make <vbg
--
Regards

Roger Govier

"Sandy Mann" wrote in message
...
"Biff" wrote in message
...
No typo.

mmmmmm......

It must just be me being the poor cousin again then <g

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

with @tiscali.co.uk

"Biff" wrote in message
...
No typo.

If I format a cell as mmm-yy and type in Nov-06 Excel (2002)
shows
it
in the formula bar as 11/6/2006.

If I don't preformat the cell and type in Nov-06 Excel
automatically
converts it to 6-Nov and shows 11/6/2006 in the formula bar.

Biff

"Sandy Mann" wrote in message
...
If I type in Nov-06 Excel evaluates it as Nov 6 2006 not Nov
2006

I assume that it is a typo because my XL97 evaluates Nov-06 to
November 1 2006 not the 6th

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

with @tiscali.co.uk

"Biff" wrote in message
...
Hold on there a second.......

In column A I have the month and Year "Nov-06".

If you have dates like Nov-06 how do you know what day of the
month
that is?

If I type in Nov-06 Excel evaluates it as Nov 6 2006 not Nov
2006

Seems to me that you should use the full date.

Biff

"Biff" wrote in message
...
Try this:

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(DAY(A1:A10)<12),C1:C10)

Biff

"Raj" wrote in message
...
I am new to using formulas in Excel and need some help with
using
SumIf and a
date.
In column A I have the month and Year "Nov-06". I would
like to
do is have
Column C Sum only if the current day is < 12 of the month.
Thanks