+ Reply to Thread
Results 1 to 14 of 14

Formulas not working with double digit dates

  1. #1
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Post 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.
    Attached Files Attached Files
    Last edited by ckk403; 12-30-2010 at 10:31 AM. Reason: Solved

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Formulas not working with double digit dates

    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,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Formulas not working with double digit dates

    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!
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Formulas not working with double digit dates

    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.

  5. #5
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Formulas not working with double digit dates

    =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!

  6. #6
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: Formulas not working with double digit dates

    I apologize I believe there was some confusion. The error is occurring on the % Category Summary tab where it isn't pulling in percentages for dates that have double digits. In regards to excel not recognizing them as dates, I have set E and F on the Paste Data tab as dates, and then retyped in the dates. Then on the % Category Summary tab I formatted the dates column as dates and retyped in the date, still didn't resolve the issue.

  7. #7
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: Formulas not working with double digit dates

    Oh and what in the world is this:

    =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)))

    I can't say I have heard of the INT or MID formulas :-\

  8. #8
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: Formulas not working with double digit dates

    Okay, I did some research on the Int and Mid, and I pasted into the worksheet. Attached is the results. The % Category Summary still isn't pulling in data.
    Attached Files Attached Files

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Formulas not working with double digit dates

    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.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Formulas not working with double digit dates

    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:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: Formulas not working with double digit dates

    On the % Category Tab in cell D11. This cell needs to count how many times there is a 1 on the Paste Data tab in the "Subject Code" column, but only for the date to the left of D11 (A11) which is 12/10/10. If I manually count (highlight and look at the bottom of the screen) it says that for 12/10/10 there are 39 errors in the subject codes column (the 1's are errors). From there the cell is to take the amount of errors divided by the count of records for that day. The count of records come from B11 (% category summary) where a formula counts how many times 12/10/10 is pasted in on the Paste Data. The formula should be 39/173 to give a percentage of 22.54%. 39 is how many subject code errors there are for 12/10/10 (paste data tab), and 173 is the count of how many times 12/10/10 appears on the paste data tab.

    Then from there the formula just moves from subject codes to disposition, abbreviation...etc. If there are any question please do not hesitate to ask :-)

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Formulas not working with double digit dates

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

    Please Login or Register  to view this content.
    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
    Last edited by DonkeyOte; 12-30-2010 at 10:22 AM.

  13. #13
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: Formulas not working with double digit dates

    DonkeyOte... let me just say. Oh. My. God. I knew it was something easy, I thought check my ranges backwards and forwards... apparently I did not. Because you know where the range ended in the E column? The last day of 12/09/10, which means 12/10/10 wasn't pulling in data. You are life saver and I am an idiot. I appreciate the eye opener. Your guys are all awesome and I appreciate the help.

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Formulas not working with double digit dates

    A separate set of eyes does wonders.

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

    Please Login or Register  to view this content.
    Then, in terms of avoiding double evaluation etc...

    Please Login or Register  to view this content.
    You can then apply a Custom Format to B2:C32 of: #;-#;;@
    this will mask the 0's as Blank

    Then

    Please Login or Register  to view this content.
    hope that makes sense

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1