Format for making date calculations
 

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.

Format for making date calculations
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Cassidy1" wrote in message
...
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.

Format for making date calculations
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Cassidy1" wrote in message
...
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:K1 47="V"))

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Cassidy1" wrote in message
...
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.

Format for making date calculations
 

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!

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Cassidy1" wrote in message
...
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:K1 47="V"))

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Cassidy1" wrote in message
...
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.