Results 1 to 12 of 12
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Today date in range of dates (Excel 2003)

    I have a workbook with cols for various due dates (draft 1, draft 2, final, etc.) and I want to have a col that tests if today's date is in the same week as any of these due dates. So if today is Aug 25 and I have dates in these cols of Aug 7, Aug 22 and Aug 30 then I want to write 'x' in this osther cell to show that this row of data has a delivery date in the current week. It's meant to be a quick visual that something is due this week whenever they view this worksheet.

    I went through the various date formulas but nothing really jumped out so I suspect it's a combo of formulas. I'd rather not use the Analysis Toolpak as I can't depend on others to install this add-in.

    Thnx,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Today date in range of dates (Excel 2003)

    Say that your dates are in columns C through H, starting in row 2. In another cell in row 2, enter the following array formula (confirm with Ctrl+Shift+Enter):
    <code>
    =IF(SUM(1*(C2:H2-WEEKDAY(C2:H2)=TODAY()-WEEKDAY(TODAY()))),"X","")
    </code>
    Fill down as far as needed.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Today date in range of dates (Excel 2003)

    Ok looks great... I tried this but couldn't get anything to display. I changed some dates to be due this week (same week as Today()) but it doesn't work. I did do the Ctrl+Shift+Enter. Attached is a sample with all 'secret' stuff deleted. Can you please see what I did wrong?

    Thnx,
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Today date in range of dates (Excel 2003)

    It does work when I open your workbook - see screenshot below. But ...
    1) Calculation has been set to Manual, so the formulas aren't updated correctly.
    2) There are some dates lower down in column I that have been entered as text instead of as a date, for example "Aug. 21".
    3) There are also some cells in column I contain comments such as "Complete" or "Ongoing".
    The formulas can't handle 2) and 3)

    Set calculation to Automatic, even if only temporarily.
    Optionally, correct the dates-as-text.
    Enter the following monster formula in K5, again as an array formula:

    =IF(SUM(1*IF(ISERROR(WEEKDAY(D5:I5)),0,D5:I5-WEEKDAY(D5:I5)=TODAY()-WEEKDAY(TODAY()))),"Yes","")

    and fill down as far as needed. It'll allow the use of comments in the date columns.

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Today date in range of dates (Excel 2003)

    <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22> Ahhh yes I had forgotten that I set Calc=manual as I have many array formulas on other sheets and I got tired of waiting for those to calc while I was doing this. I'll get the people who update this sheet to either enter real dates or nothing instead of text like "Ongoing".

    Thanks for setting me straight, this will help a lot for tracking some documents that are needed for some big launches coming up (and I can't convince them to use MS Project).

    Thnx,
    Deb

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Today date in range of dates (Excel 2003)

    How would I change it to say "Yes" for dates in the previous and next week from Today(). After working with your solution a bit (which is what I had asked for), I realize it's better to know if an item was late or is due next week. I tried an OR statement with as an array formula (editing yours) but it didn't work as everyting was "Yes"

    "Late" = OR(DateRange < Today()-8, ... , DateRange < Today()-14)

    "Soon" = OR(DateRange > Today+1, .... DateRange > Today+7)

    If this is too unwiedly w/o the Analysis Toolpack, I am willing to have the few users add it (I'll do it for them) so that may give you better (and less complex) solutions.

    Thnx,
    Deb

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Today date in range of dates (Excel 2003)

    I'm sorry, your description is not clear to me.

    Do you want column K to contain "Soon" if any of the cells in columns D through I contain a date in the next week, and "Late" if any of the cells in columns D through I contain a date in the previous week? What if there are both dates in the previous week and in this week, or in the previous week and in the next week, or in all three?

    Please try to be as specific as possible.

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Today date in range of dates (Excel 2003)

    Yes, my description is a bit lame. My goal is to notify the user of this table that any given deliverable (whitepaper, PDF, video, etc.) is due within one week of today's date (+/- one week). Let's leave out the "late" and "soon" and just leave it as "Yes" if Today() is within that 21 day period (one week prior, one week after and the current week). Now that I say this, maybe all I need to do is subtract the dates from Today() and if the ABS(Today() - dateRange) <= 21 then that's a "Yes".

    I tried using an OR() with the date ranges but that didn't work as an array formula.

    Thnx
    Deb

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Today date in range of dates (Excel 2003)

    Do you prefer to look at dates within n days of today (n to be specified), or do you prefer to look at dates in the previous, current or next calendar week?

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Today date in range of dates (Excel 2003)

    If you can figure out how to make it 'n' that'd be perfect ('n' can be a named range or in some hidden cell) but if that makes it too hairy, I'm content with a 21 day window around Today(). I do have another col for %Complete which will be used in conjunction with the DueDate that you're helping me with.

    Deb

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Today date in range of dates (Excel 2003)

    You can use Insert | Name | Define... to create a named range DateWindow that refers to the constant value =7 (for example) or to a cell (which may be hidden) that contains the value for n.
    The array formula then becomes
    <code>
    =IF(SUM(1*(ABS(D5:I5-TODAY())<=DateWindow)),"Yes","")
    </code>
    See attached workbook (I removed all text values from columns D:I and repaired dates entered as text).

  12. #12
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Today date in range of dates (Excel 2003)

    This is perfect!! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>. It has the right amount of flexibility and I can combine it with my Perc_Complete col as well for an extra filter.

    Deb

Posting Permissions

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