Thread: Formulas not working with double digit dates

I am lost, and confused. I have deleted all of the personal and all that is left is what the formula needs.

I have formulas that will count how many times the date is mentioned. Then how many times a 1 (or in one column a 2) is counted. The numbers are errors that employees made. So it takes how many times the date was counted (total records for that day) then how many times errors happened in that day. It takes these two numbers and gets a percentage. I am using IF(ISERROR, as well as COUNTIF and SUMIF. I don't think the problem is with the formulas, but what is happening is that on dates with double digits (12/10/2010 - 12/31/2010) it is bringing in zeros. However, 12/01/2010 - 12/09/2010 is working perfectly.

I have tried reformatting the dates to be numbers and still nothing. Something strange was this happened once, went home for the night, went back to work the next day and was getting ready to fix it when I realized it was already fixed. Then today it isn't working again and apparently it hasn't been working for a week or so. When I opened up the document it had data and percentages being pulled in from 12/01 - 12/16, then after saving the file it appears to have "re-calculated" and the data that was being pulled in for 12/10 - 12/16 vanished (went back to zeros). I believe looking at the spreadsheet it is self explanatory.

As I have mentioned I am not seeing anything wrong with formulas and I have no idea how to fix it. :-\ At work I have excel 2003, on my home computer I have 2007. I had a file that was 10mb and it wouldn't let me upload a zip file so I had to convert it and delete thousands of rows of data. If anyone can help I would GREATLY appreciate it. And please don't recommend I use a pivot table. The actual file is usually 60mb and adding another pivot will make it a lot larger. There was data all the way up to the 24th but in order to be able to upload I had to remove a lot of the data.

Hi,

I don't know if this is part of the problem (or just me), but neither column "E" or "F" are being recognized as dates by Excel.

Have a look with each version of Excel you're using to ensure the dates are being recognized as dates, and not just text strings that look like dates.

Cheers,

Your dates in column E are text, not dates!

=IF(ISERROR(LEFT($F3,10)),"",--LEFT($F3,10))

use this instead and format the cell to date!

Can you outline the issues in the sample - from what I can see it works ok assuming you are running on the US Regional Locale.

FWIW, COUNTIF will treat numbers stored as text as numbers so the fact E is returning date strings rather than dates is not necessarily a problem - locale config. is all important however.

=if(iserror(INT(MID(F4,7,4)&"/"&MID(F4,1,2)&"/"&MID(F4,4,2))),"",INT(MID(F4,7,4)&"/"&MID(F4,1,2)&"/"&MID(F4,4,2)))

Will work with any region settings!

Rather than us sanity checking the matrix can you pin point a specific cell in the results tab that is not correct and outline expected result ? This will save time for all.

Presumably you're aware your summary functions aren't referencing all of the source data - ie the precedent ranges do not encompass all rows.

SUMIF is sufficiently efficient that entire column references are not an issue (it works with used range intersect) ... using Col F as an example:

F2:
=IF(SUM($C2),SUMIF('Paste Data'!$E:$E,$A2,'Paste Data'!$Q:$Q)/$C2,"")
copied down

The sample would imply slightly different results for D11 but reiterating prior post re: insufficient precedent ranges and efficiencies etc...

D11:
=IF(SUM($C11),SUMIF('Paste Data'!$E:$E,$A11,'Paste Data'!$O:$O)/$C11,"")

on an aside you would be far better off letting Cn be 0 and using a Custom Format to mask the 0 as blank than double evaluate the formula
also it would be better to aggregate O:V in X and using a single column summation

A separate set of eyes does wonders.

To clarify prior points... I would be inclined to do the following:

'Paste Data'!X3:
=SUM($O3:$V3)-$P3/2
copied down

Then, in terms of avoiding double evaluation etc...

'% Category Summary'!B2:
=COUNTIF('Paste Data'!$E:$E,$A2)
copied down to C32

'% Category Summary'!C2:
=SUMIF('Paste Data'!$E:$E,$A2,'Paste Data'!$X:$X)
copied down to C32

You can then apply a Custom Format to B2:C32 of: #;-#;;@
this will mask the 0's as Blank

Then

'% Category Summary'!D2:
=IF(SUM($C2),SUMIF('Paste Data'!$E:$E,$A2,'Paste Data'!$O:$O)/$C2,"")
copied down to D32

'% Category Summary'!E2:
=IF(SUM($C2),SUMIF('Paste Data'!$E:$E,$A2,'Paste Data'!$P:$P)/$C2/2,"")
copied down to E32

repeat construct for other columns etc...

hope that makes sense