+ Reply to Thread
Results 1 to 2 of 2

IF statement for two conditions of dates - prior date and then prior three days

  1. 02-26-2015, 05:18 PM #1
    LALTIZER
    LALTIZER is offline
    Registered User
    Join Date
    05-25-2012
    Location
    Virginia beach, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    57

    IF statement for two conditions of dates - prior date and then prior three days

    This formula =COUNTIF(Data!A:A,E1-1) is correct if the day of week is Tuesday, Wednesday, Thursday., or Friday, but I need another condition to bring back the counts if it is Monday then the count should be the prior three days counts (Sunday, Saturday, and Friday).


    I have created this IF statement below for both conditions, but it is not working correctly:

    =IF(F1="Monday",(COUNTIF(Datal!A:A,E1-3)-COUNTIF(Data!A:A,E1-1)),COUNTIF(Data!A:A,E1-1))

    It only brought back Sunday count

    02/23/15 Monday

    Daily Count Daily Amt
    4 $217.827.84

    I have attach an excel spreadsheet with the data to help.

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-26-2015, 06:12 PM #2
    skywriter
    skywriter is offline
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: IF statement for two conditions of dates - prior date and then prior three days

    I'm getting an answer of 1, which looks right to me based on your data. What's the issue? The date in E1 2/23/2015 is Monday so the formula is counting the number E-3 which is 2/20/2015 which is 5 and subtracting the number of E-1, which 2/22/2015 and there are 4 of those in your data, so the result is 1.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Date Countdown with colors for expired and 30 days prior
    By jnblue in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2013, 11:29 AM
  2. sending an email 7 days prior to a due date
    By tgazzi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-24-2013, 12:28 AM
  3. Set alert 30 days prior to date
    By joshpyles12 in forum Excel General
    Replies: 1
    Last Post: 02-01-2012, 07:40 PM
  4. Excel 2007 : Need to know how to set an alert to show 30 days prior to the date in each cell.
    By joshpyles12 in forum Excel General
    Replies: 1
    Last Post: 02-01-2012, 07:14 PM
  5. shade a cell 3 days prior to an appointment date?
    By Ltat42a in forum Excel General
    Replies: 3
    Last Post: 07-07-2008, 04:37 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1