+ Reply to Thread
Results 1 to 10 of 10

Find number of days greater than zero between date range.

  1. #1
    Registered User
    Join Date
    08-30-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2013 Trial
    Posts
    8

    Question Find number of days greater than zero between date range.

    Hi All,

    I had to calculate the total number of heating degree days (HDD) and cooling degree days (CDD) between 05/01/2008 and 30/04/2009, which I did.
    The question is, how do I find the number of HDD and CDD between any given date range. For example number of HDD and CDD between 08/13/2008 and 10/20/2008, the date range can be anything. I am using excel 2013.
    I have attached the xls file. There is no sensitive data. Please see sheet 2.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find number of days greater than zero between date range.

    From how many degrees is the day warm?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    08-30-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2013 Trial
    Posts
    8

    Re: Find number of days greater than zero between date range.

    Heating degree day (Hdd) = 65 - Average temperature of a day. For example for 5/1/2008 Average (rounded (Row C)) is 51, therefore HDD = 65 -51 = 14
    Cooling degree day (Cdd) = Average temperature of a day -65. For example for 5/7/2008 Average (rounded (Row C)) is 66, therefore CDD = 66-65 = 1

    Thanks

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find number of days greater than zero between date range.

    Not sure that i get it but let's try.

    In K2=13/08/2008. In L2=20/10/2008

    Then this formula gives 40 as result. Is correct?

    =SUMPRODUCT((A3:A1000>=K2)*(A3:A1000<=L2)*(E3:E1000>0))

  5. #5
    Registered User
    Join Date
    08-30-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2013 Trial
    Posts
    8

    Re: Find number of days greater than zero between date range.

    Hi Guru,

    It worked for cooling degree days (CDD) there are 40 CDD between 08/13/2008 and 10/20/2008.
    How about heating degree days (HDD) between range 10/20/2008 and 11/13/2008.

    Thanks, you are awesome.

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find number of days greater than zero between date range.

    24 ??

    =SUMPRODUCT(($A$3:$A$1000>=$K$2)*($A$3:$A$1000<=$L$2)*($D$3:$D$1000>0))

  7. #7
    Registered User
    Join Date
    08-30-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2013 Trial
    Posts
    8

    Re: Find number of days greater than zero between date range.

    It is 25 including the dates 10/20/2008 and 11/13/2008.

    Thanks

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find number of days greater than zero between date range.

    Are you sure?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-30-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2013 Trial
    Posts
    8

    Re: Find number of days greater than zero between date range.

    Hi Guru,

    I was looking at date range from October 20, 2008 to November 13, 2008. The file you attached was from September 19, 2008 to October 20, 2008. For this range yes the answer is 24.
    You are correct. Thanks very much for your help. You are great

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find number of days greater than zero between date range.

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find lowest number greater than 0 but not in a range
    By jdykstra in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-11-2013, 05:53 PM
  2. [SOLVED] calculates number of days less than or greater than x (a specific date)
    By t2dela in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-07-2013, 10:11 AM
  3. [SOLVED] Find date using start date and number of network days
    By v!ctor in forum Excel General
    Replies: 4
    Last Post: 07-06-2012, 05:27 PM
  4. Replies: 6
    Last Post: 12-12-2011, 09:07 PM
  5. [SOLVED] Number of Days in a Date Range
    By tesouthworthjr in forum Excel General
    Replies: 6
    Last Post: 07-14-2005, 05:06 PM

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