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.
<tbody>
</tbody>
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.
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 |
<tbody>
</tbody>
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!