PC date format (M, MM vs. MMM) messes up formula...

[KR]

We have over 200 Excel files on a server (for different areas of the
company) that include the formula below. Our computers, by default, have the
windows date setting of M/d/yyyy (or some may have MM/dd/yyyy?). Our
organization just changed policy to avoid day/month vs month/day confusion
with dates, and now all PCs must have the month spelled out, such as
MMM-dd-yyyy or dd-MMM-yyyy.

The problem is that when a PC's system date setting is changed to MMM
instead of MM or M, the following formula no longer works. The original
intent of the formula is to identify all entries that were made in the
current quarter, and sum up the totals

{=SUM(IF(TRUNC((MONTH(A$12:A38)-1)/3,0)+1=TRUNC((MONTH(NOW())-1)/3,0)+1,F$12
:F38,0))}

Where Column A has the date the entry was made, and Column F has the value
being summed.

I used "evaluate formula" and it comes up with #value for MONTH(A$12:A38)
when the system setting is MMM, but calculates fine when using MM or M

Unfortunately, the data being pushed into column A (from a userform) may
have to segue over to MMM format as well for compliance to the policy, so in
addition to finding a way to make the formula work when the machine system
setting is changed, I need the formula to work under either system setting,
and also regardless of the format of Column A.

Our users are all using Excel 2003, on either Win2000 or WinXP.

Any suggestions would be _greatly_ appreciated!!
Thanks,
Keith

 

[Dave Peterson]

Formatting of the cells to show the long month name won't cause your formula to
break.

But text that is masquerading as a date could.

If you put this in an empty cell:

=count(a12:a38)
do you get the same as:
=counta(a12:a38)

Dates are just numbers and =count() counts numbers.

So if you fix those fake dates, your formula should work.

 

[Tom Ogilvy]

when I put a date value in the cell and used the formats you described, the
month function worked fine:

05/23/2006 5
23-May-06 5
May-23-2006 5
23-May-2006 5

the 5's were produces with =Month(A1) type formula

Perhaps you just need to make sure your dates are stored as dates and not
strings.

 

[KR]

I have a monthview calendar control that pops up from a userform, with the
code:
TempDate = MonthView1.Value

Then that information gets written to the spreadsheet via:
Sheet1.Range("A13").Value = frmEntry.lblD2

since it is pulling this value from the label (DOH!) it is coming in as
text- I just hadn't picked up on that, since the M and MM formulas were able
to function off the text string.

Now I've got to figure out how to either (a) cycle all the workbooks and
change them all to dates, and fix the code in each workbook so it won't keep
happening, or, (b) come up with some way to pull what I need from the text
strings. Either way, I think I'm in for some pain. :-/

Thanks Tom & Dave, for pointing out the text issue.

Best,
Keith