formula to sum hours if
Results 1 to 6 of 6

Thread: formula to sum hours if
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default formula to sum hours if

    Hello all!

    I have a spreadsheet at work that the techs enter in on/off times for equipment. I need to know how many hours the equipment has ran that day.

    Date Initals OFF ALL DAY? ON ALL DAY? INITIAL SHUT DOWN INITIAL START UP SECONDARY SHUT DOWN SECONDARY START UP
    5/1/2019 BB 11:00 18:30
    5/2/2019 BB YES


    Data such as the above. All times are put in using a 24hr clock. The on/off all day columns are drop down lists for YES or NO.

    Is there a formula such that I can put in 0 hrs if YES off all day, 24 hrs if YES on all day, and then if neither of those are true, calculates the hours operated that day? I've tried a couple versions and it doesn't seem to pull through correctly for all cells. I don't think I was nesting the IF statements correctly. What I was using for everything excluding IFs was requiring additional columns that stated start of day was 0:00 and end of day was 23:59, and taking all the columns subtracted from another and * 24. IE this formula: =((J327-I327)+(L327-K327)+(N327-M327))*24


    Appreciate the help!

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: formula to sum hours if

    Hi, below should work for you:

    =IF(E2 <> "Yes",IF(D2 <> "Yes",24*(1-G2-I2+F2+H2),0),24)
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    592
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula to sum hours if

    I think the columns were a bit off. Is this correct?

    BCDEFGHIJ
    1DateInitialsAll OffAll OnInit ShutInit StartSec ShutSec StartTOTAL
    25/1/2019BB11:0018:3019:0020:008.5
    35/2/2019BBYES24
    45/3/2019XXYES0
    55/4/2019AA9:0013:004

    Sheet2



    Worksheet Formulas
    CellFormula
    J2=IF(E2 <> "Yes",IF(D2 <> "Yes",24*(G2+I2-F2-H2),0),24)


  4. #4
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula to sum hours if

    There is a spacer column between initials & All Off - there's actually 5 pieces of equipment here, so one date column for all 5, and a spacer column between each equipment piece. There's about 20 people that enter data into this spreadsheet, so it's an easy visual break for where they need to be entering in data.

    The formula worked!!! Thank you so much! I think I was way, way overthinking it.

    For background - this all used to be handwritten & hand calculated.....which is a heck of a lot of time spent doing this. Thanks again!

    There's only one so far that seems off - I have an initial shutdown of 23:30 (no other numbers given) which should return a time on of 23.5, and it's showing 47.5.

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    592
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula to sum hours if

    In my formula, if Initial shutdown is 23:30 and there is no initial start-up, it shows -23.5. (can you "shut down" without a "start-up"?)

  6. #6
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula to sum hours if

    Quote Originally Posted by kweaver View Post
    In my formula, if Initial shutdown is 23:30 and there is no initial start-up, it shows -23.5. (can you "shut down" without a "start-up"?)
    Yes - the equipment was already running from the previous day. So for that day, it should be treated as though start up was 0:00, but in reality the equipment was started up four days ago.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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