Help with SumIf

R

Raj

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
 
B

Biff

Hold on there a second.......
In column A I have the month and Year "Nov-06".
Click to expand...

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
 
S

Sandy Mann

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

[email protected]
[email protected] with @tiscali.co.uk
 
B

Biff

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
 
R

Roger Govier

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>
 
P

Pete_UK

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
 
S

Sandy Mann

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

[email protected]
[email protected] with @tiscali.co.uk
 
S

Sandy Mann

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

[email protected]
[email protected] with @tiscali.co.uk
 
P

Pete_UK

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
 
B

Biff

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.
Click to expand...

Your reasoning is correct (as usual!)

Biff
 
Top