formula to sum hours if

KGIL789

New Member
Joined
Nov 20, 2017
Messages
14
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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Initals[/TD]
[TD][/TD]
[TD]OFF ALL DAY?[/TD]
[TD]ON ALL DAY?[/TD]
[TD]INITIAL SHUT DOWN[/TD]
[TD]INITIAL START UP[/TD]
[TD]SECONDARY SHUT DOWN[/TD]
[TD]SECONDARY START UP[/TD]
[/TR]
[TR]
[TD]5/1/2019[/TD]
[TD]BB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11:00[/TD]
[TD]18:30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/2/2019[/TD]
[TD]BB[/TD]
[TD][/TD]
[TD][/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi, below should work for you:

=IF(E2 <> "Yes",IF(D2 <> "Yes",24*(1-G2-I2+F2+H2),0),24)
 
Upvote 0
I think the columns were a bit off. Is this correct?


Book1
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
Cell Formulas
RangeFormula
J2=IF(E2 <> "Yes",IF(D2 <> "Yes",24*(G2+I2-F2-H2),0),24)
 
Upvote 0
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.
 
Upvote 0
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"?)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top