Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    14
    Thanks
    2
    Thanked 1 Time in 1 Post

    countifs and using a date from another cell

    I am writing a template for quarterly reports using countifs to parse data fields. The first step of the formula selects all data existing between two dates. The rest of the formula looks for other data. There is no problem if I put the dates right into the formula but what I would like to do is put the start and end date into separate cells so that the formulas simply the cell references in the formula and don't need to be changed with each report.
    So far all I get is error messages. I have tried both cell reference and naming the cells. I can't tell whether it is simply my ignorance about formating the function or it simply can't be done. Search on the net hasn't helped. Any thoughts would be appreciated.

    BTW Dates are notoriously tricky. I discovered when I moved the formula to another person's machine I got all zero results. The default date setting for that machine was not the dd/mm/yyyy that I was using. The cells were I had typed in my start and end dates [as dates] got automatically reformatted. The formulas, however, needed to be rewritten in the new format to produce results. I am suspicious that this is where the problem lies.
    thanks
    Glenn

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Glenn:

    Welcome to the Lounge.

    Since you did not post an example I made a few assumptions.

    I think the whole problem is that any cell with a date is a number. However, Countif likes text for criteria.

    So my best suggestion is to put ajacent to the date a string formula that turns the date into text.

    See the attached.

    I hope this helps

    TD
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you post an example workbook?

    Steve

  4. #4
    New Lounger
    Join Date
    Nov 2012
    Posts
    14
    Thanks
    2
    Thanked 1 Time in 1 Post
    TD
    thanks for the reply. Ironically, I got up this morning and finally read an email from a list serve I belong to about using ACCESS. The article was about the peculiarities of trying to work with dates from OUTLOOK in ACCESS because of this same problem. Then I got your e-mail. My aha! from this was the same as yours except that I was going to turn the date into a string rather than a number. I haven't gotten to it yet so I don't know for sure if it will work but it means that the whole process could be contained within the original formula. If that fails, yours will be my salvation.
    thanks
    glenn

  5. #5
    New Lounger
    Join Date
    Nov 2012
    Posts
    14
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Steve
    thanks for the interest. I may have several solutions. If you are curious check my reply to TD
    thanks
    Glenn

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you are going to convert dates to strings and want to do comparisons, you should convert to a format like "yyyy/mm/dd" to ensure that the comparisons go Year to month to day. If you use something like "dd/mm/yyyy" your primary comparison will be day of the month. so for example the 2nd of any month, any year will be later than the 1st of any month and any year [02/01/1901 > 01/12/2013 when sorted as text...]

    Steve

  7. #7
    New Lounger
    Join Date
    Nov 2012
    Posts
    14
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Steve
    Good point. The trouble first arose with my template when it was moved to another machine that had the date formatting different to the one I created the formula on.
    The original formula looked like this [where "paste data here" was the name of the sheet with the data to be parsed.
    COUNTIFS('paste data here'!$Y:$Y,">=01/04/2013",'paste data here'!$Y:$Y,"<=30/06/2013",'paste data here'!AE:AE,">=1")

    My quest for a way around this was to let the user type the date into a cell and let the formula use that reference.
    I had just rewritten the formula to look like this when I got your e-mail

    =COUNTIFS('paste data here'!$Y:$Y,">="&TEXT(B2,0),'paste data here'!$Y:$Y,"<="&TEXT(C2,0))

    This did yield the correct answer however I like your idea of using =TEXT(C2,yyyy/mm/dd) as a way of enforcing standards. As I am writing this, however, I wonder if this will war with the underlying default date setting that caused an error in the first place. If the owner of the computer [who created the original data that is going into the "past date here" sheet also sets the date in the cell that my formula references and I use the default setting of zero in the TEXT function then won't we have a closed universe where the cell reference and the data formatting will match?

    The only other flaw I can see here, in my attempt to make this foolproof for non technical users, is if the person who uses this template enters a date in a format that Excel doesn't recognize as a date. A lot of people working with Excel won't recognize that their "date| is actually text.

    In any case thanks for the suggestion. I will play with both formatting versions of TEXT and see how and if we can make Excel stumble
    Glenn
    Last edited by glenn questions; 2013-07-09 at 17:32.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can try to enforce by using data validation (or cond formatting to highlight) non numeric entries which will flag "Text-dates" (text that Excel does not recognize as a date) and format the cells to a date format. I recommend using something with with "mmm" to display part of the month name and not "mm" which only displays the 01-12 to avoid confusion of US vs European styles (dd/mm vs mm/dd)

    Steve

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You shouldn't have to convert to text:
    =COUNTIFS('paste data here'!$Y:$Y,">="&B2,'paste data here'!$Y:$Y,"<="&C2)

    should work regardless of format (the underlying numbers will remain the same if they are true date values)

    For your original fixed values, using the date function should avoid internationalisation issues:

    =COUNTIFS('paste data here'!$Y:$Y,">="&DATE(2013,4,1),'paste data here'!$Y:$Y,"<="&DATE(2013,6,30),'paste data here'!AE:AE,">=1")
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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