lookup an column and locate the first value greater than 0 and return the hh:s

[superpurple]

Hi guys, I have troubles finding correct formulas in excel and I hope that someone can assist me here.
What I am trying to do is to lookup an column and locate the first value greater than 0 and return the hh:ss located in the same row but on different column.
Here is the idea:

A B C D E
1 01/01/2022 11:55:00 AM 0 0 0 0
2 01/01/2022 12:00:00 PM 0 1 1 0
3 01/01/2022 12:05:00 PM 1 2 2 0
4 01/01/2022 12:10:00 PM 2 3 3 1

Return should be:
- column B = 12:05
- column C = 11:55
- column D = 11:55
- column E = 12:10

If there is no value greater than 0 in the column, the return should be "OFF"

I was trying =INDEX($A2:$A289;MATCH(B2;B2:B289;1))+B293 -> but the formula is not working all the time (refer to attachment in CELL D888). Also I have to add up 00:05 hours to the formula to get the correct result. I was not able to implement OFF condition.

Further, I am looking for a same formula to locate the last value greater than 0 in the same column, and return similar as with above.

A B C D E
1 01/01/2022 11:55:00 AM 1 1 1 1
2 01/01/2022 12:00:00 PM 2 0 2 2
3 01/01/2022 12:05:00 PM 0 0 3 3
4 01/01/2022 12:10:00 PM 0 0 0 0

Return should be:
- column B = 12:00
- column C = 11:55
- column D = 12:05
- column E = 12:05

If there is no value greater than 0 in the column, the return should be "OFF"

For this I was trying =IFERROR((LOOKUP(2;1/(B2:B289>B3);$A2:$A289));"OFF") -> I believe that this formula works, but please review it.

__

From time to time I have missing data (see column K), how this can be fixed in formula to take the first value?

Thank in advance!

Regards

[AliGW]

Administrative Note:

Welcome to the forum.

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 yo are new here, I have done it for you this time.)

[AliGW]

You could try this:

=IF(COUNTIF($D$596:$D$883,">"&0)=0,"OFF",D889-D888)

Administrative Note:

Members will tailor the solutions they offer to the version (NOT a release number like 2022) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[superpurple]

Dear Ali,

Thank you for your feedback and dully noted on your comments!

On the formula, I believe that the formula is to calculate the running hours, no? Currently I find it working to calculate the "Running Hours", but for this I need to amend the "Sync Hour" and "Off-Sync Hour" formula. Any advise here?

Thank you!

Regards

[AliGW]

Thank you for your feedback and dully noted on your comments!

But you have not updated your profile - please do so NOW.

On the formula, I believe that the formula is to calculate the running hours, no? ... Any advise here?

Yes. Just adapt the formula to the ranges you want.

This is what you wanted:

If there is no value greater than 0 in the column, the return should be "OFF"

[superpurple]

Hello Ali,

I have updated.

Actually, I am looking for the following:

1. An correct formula to lookup in an column and locate the first value greater than 0 and return the hh:ss located in the same row but on different column. (as mentioned, the formula which I am using is not working all the time, refer to CELL D888)
2. An correct formula to lookup in an column and locate the last value greater than 0 and return the hh:ss located in the same row but on different column. (formula which I have used to be verified)
3. In the above two formulas to implement the OFF condition if on the lookup column there is no value greater than 0.

I have the impression that the formulas which I have used have some glitches.

Thank you!

[AliGW]

Instead of formulae that don't work, please provide, in a MASSIVELY CUT-DOWN version of your workbook, with some expected results, manually entered.

There is too much data in the current workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.