+ Reply to Thread
Results 1 to 2 of 2

SUMIFs Formula - Converting Hours to Days to use as criteria

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    Tennessee
    MS-Off Ver
    Exce2013
    Posts
    8

    SUMIFs Formula - Converting Hours to Days to use as criteria

    Hi everyone! This is my first post here but I'm a longtime lurker. I struggle with explaining excel issues, so I will try my best!

    I have 2 Sheets - my end goal is to find the number of submittals (recorded in column C - TTF tab) and sum those values if the Date Raised (column D) + Time to Fill (column B) is within the date range on my summary tab. The issue is that Time to Fill is in hours and I need it to be converted to days within the formula. I do not want to add any columns to my TTF tab because I'm trying to make my project extremely automatic without adding additional columns or using macros. That being said - I dont know if what I want can be done.

    Just for reference TTF is a table named TTFTable. I'm trying to do this through table references. Here is the code that does not work - I've attached a screen shot of the error I get. (I am trying to sum positions filled in june, so anything >5/31 and <=6/30) Whenever I open the functions tab, I do not see a #Value error. I've also attached the workbook for reference.

    Please Login or Register  to view this content.
    Thanks everybody!
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: SUMIFs Formula - Converting Hours to Days to use as criteria

    there are two reasons why your formula isn't working, and won't work even if fixed.

    1. you are manipulating the "criteria_range" portion of the SUMIFS argument (shown below), which it does not allow. it allows manipulation of the "criteria" portion, however.

    TTFTable[@[Date raised]]+(TTFTable[@[Time To Fill (Hours)]]/24)

    2. the values that you have on the Summary tab between B1 and G1 (except E1) are anything but Dates. they "look" like Dates, but they are actually Text. so, even if your formula were fixed, it would not successfully give you an output because of this fact (i.e., formula will output an error).

    so, first you have to fix the values between B1 and G1 to make them real Dates.

    then, your SUMIFS formula can be fixed in one of the following two ways:

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    however, for some godforsaken reason, either of these are not giving the "right" output and i am not able to figure out why.

    so, below is another formula that you can use, which i have tested to be correct for your sample data.

    Please Login or Register  to view this content.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

+ 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. Converting days hours minutes to hours
    By namip in forum Excel General
    Replies: 7
    Last Post: 07-10-2011, 02:00 PM
  2. Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..
    By krfarmer in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-25-2010, 05:21 AM
  3. Converting hours into days, hours & minutes
    By John754 in forum Excel General
    Replies: 3
    Last Post: 10-07-2009, 06:28 PM
  4. [SOLVED] Problem converting Hours to Days, Hours, Minutes
    By Zyzzx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-24-2005, 12:05 PM
  5. converting hours to days,hours,minutes
    By L_n_da in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-29-2005, 02:05 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