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

This is the formula I have but I am getting 0 as a result.
Thanks

"Raj" wrote:

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

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

=SUMIF(A1:A15,"<11/7/2006",C1:C15)

"Raj" wrote:

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

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

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

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

"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

Nah, just that the post isn't as clear as it could be.

Biff

"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

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

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

I got called in to work at 2am this morning even although I'm supposed to be
on holiday, (it's 4:15 am here now), and so I took the opportunity to test
it out on out companies Excel 2002 SP3 and I again got 1/11/06, (11/1/06 to
you). Perhaps there is a difference between the British and the American
versions.

--
Regards,

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


with @tiscali.co.uk


"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

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

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

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

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

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

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

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