Format for making date calculations

[Cassidy1]

I have gotten a new
computer and had all my programs and documents etc copied over to it. For
some reason, in excel, all my date formats were changed from dd/mm/yyyy to
mm/dd/yyyy and my date calculations are not working now. I have gone in to
"format cells" and set the format to custom which changed my date fields back
to dd/mm/yyyy, but my calculations still don't work.

 

[Bernard Liengme]

In Start | Control Panel, open Regional Setting and set the date format the
way you want it.
The actual format of dates in Excel should have no effect on how functions
works since dates are actually stored as numbers like 39869.
Tell us about the non-working formulas
best wishes

 

[Cassidy1]

This is the formula I'm using (below)- it calculates the number of entries
for a certain month and in a certain community. My dates are entered as
dd/mm/yyyy and the community is entered as either "N" or "D"
=SUMPRODUCT((TEXT(J2:J147,"mmmyy")="Mar08")*(K2:K147="V"))

 

[Bernard Liengme]

Have you checked that your really have dates (not text)?

Also you might try
=SUMPRODUCT((MONTH(J2:J147) =3)*(Year(J2:J147)=2008)*(K2:K147="V"))
to test for a March date in 2008

Happy to look at a file (remove TRUENORTH. from my email address)
best wishes

 

[Cassidy1]

Hi again,
I shut down my excel and reopened the program (which I didn't do after I
changed the date format in the cells) and it seems to be working correctly
now. I also changed the date format in the regional settings so I'm not
really sure what fixed this problem, but it's definitely working now. Thanks
for everyone's help!