Need a formula to determine the date the unit was last switched on

1-May 3.5
2-May 3.5
3-May 3.5
4-May
5-May
6-May
7-May
8-May
9-May 3.5
10-May 3.5
11-May 3.5
12-May 3.5
13-May 3.5
14-May 3.4
15-May 3.4
16-May 3.5
17-May
18-May
19-May
20-May
21-May
22-May
23-May
24-May
25-May
26-May
27-May
28-May
29-May
30-May
31-May

Listed above is my data. These are logs we take each day. I want a formula to detmine the date the unit was last switched on (last time it first had data (not the first or last time it had data) No data means it was not on
I need to get the result 9th May as the last time it was switched on and then it ran for 7 days (9th to 16th) The second part is fairly simple. but I am really struggling with the first part due to the fact it can be on multiple times in one month.
I could do this simply via PHP but I am struggling with excel formula

Administrative Note:

Welcome to the forum.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Also, there is a typo in your forum profile - there is no Excel 360, so please change it to 365.

You need to read our rules - generic thread titles are NOT permitted. I shall change it for you this time.

Finally, there are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

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

Try this:

=LET(l,LOOKUP(2,1/(B1:B31<>""),ROW(B1:B31)),MAX(IF(ROW(B1:B31)<l,IF(B1:B31="",A1:A30))+1))

Please update your location as requested - a very general indication is fine. This is important - "The Sea" is not in any way helpful and does not indicate which type of locale you might be using. Thank you.

Any questions?

Another way, please try

Formula:

Please Login or Register  to view this content.

Thank you HansDouwe <3 This is the perfect one. Works even if there is only one time on. Thank you

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

By the way:

I am all over the place. Internet comes and goes depending on weather haha.

That's not the point! Your location is there so that we have a clue as to your locale. Are you using a UK/US or European locale? Decimal points or commas? DD/MM/YYYY or M/D/YYYY? These things matter.

OK - fair enough. Then "At Sea with Various Locales" would actually be more useful in your profile than "The Sea". If you leave it as it is, then you are going to get asked frequently to clarify it.