Calculate total engine time (cell R2) when the engine mode is ON

**ccfong** · 02-19-2022, 02:35 PM

Greetings!

I cannot figure out how to calculate total engine time using a formula (the value should be 1.5 hours). If you look at the "Logbook" worksheet, you'll see a "Time" and "Engine Mode" column with information. Basically, I have to calculate the total engine time (cell R2) when the engine mode is ON i.e. engine mode is ON at 8:00 until 9:00, and ON again at 10:00 until 10:30.

Any help on how to approach this would be greatly appreciated!

Thank you.
CC

**AliGW** · 02-19-2022, 02:36 PM

Welcome to the forum.

Which version of Excel do you have?

Please tell us what result you are expecting in the sample file (value).

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

**ccfong** · 02-19-2022, 02:40 PM

Thank you.

I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20186) 64-bit

**AliGW** · 02-19-2022, 02:41 PM

Thanks. Please change your forum profile to state MS365 - it is clearly wrong.

Pleae also tell us the result (value) you are expecting in the sample file.

**ccfong** · 02-19-2022, 02:44 PM

Sorry about that - I use both, but this file was made on my laptop with MS365.

The value that I'm expecting in the sample file is total engine time: 1.5 hours.

**MrShorty** · 02-19-2022, 04:54 PM

It seems like it should be as simple as the difference between "off" times and "on" times. Something like =SUMIFS(B13:B100,L13:L100,"off")-SUMIFS(B13:B100,L13:L100,"on"). This will result in a cell value the represents "days". You can then format as elapsed time (something like [h]:mm). Or, if you would prefer the output to be in decimal hours, it's a simple unit conversion to get the result in decimal hours =CONVERT(formula,"day","hr") or equivalent.