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!
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!