Format for making date calculations

  • Thread starter Thread starter Cassidy1
  • Start date Start date
C

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.
 
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
 
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"))
 
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
 
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!
 
Back
Top