Google Sheets: Last Non-Empty Cell - First Non-Empty Cell

Question

Hello,  I have a simplified scheduling table for the occasion. I applied conditional formatting: the cell changes color (the text as well) when it is not empty. I want to allow the editor to put anything in the cells (text or number).  Can you help me calculate the hourly range (hours worked during the day + break) for employees? For calculating the hours worked, I have no problem.  Then, if possible, can you also help me with the calculation of break times?  I'm exploring some options: in cell BE2, the EQUIV function allows me to get the column number of the first non-empty cell. In cell BE6, I manage to get the column number of the last non-empty text cell (it doesn't work if it's a number).  Any suggestions?  My table: https://docs.google.com/spreadsheets/d/1cc9NIdSKQ1jcICDIXQ6Cb0Ftrp8hbnCG_EjFxkvXQwI/edit?usp=sharing  Thank you very much :)  Configuration: Windows / Chrome 92.0.4515.131

PapyLuc51 · Answer

Hello,  I found a matrix formula to calculate the amplitude.   =IFERROR(ArrayFormula(max(IF(B2:BC2<>"";COLUMN(B2:BC2)))-MATCH(TRUE;INDEX(B2:BC2>0;0);0))*0.25/24;"")  For the dwell time =IF(SUM(BE2;-BD2)=0;"";SUM(BE2;-BD2))  See your example file  Best regards

mafia97231 · Answer

Thank you Grandpa Luc for your reply :)  May I push a bit further and ask you for a solution to calculate working time consecutively?  Indeed, I am looking to set up a pop-up (or conditional formatting) if the employee works more than 6 hours consecutively to comply with labor law.  Thank you again.

PapyLuc51 · Answer

A macro that turns black the cell following 24 consecutive values (so 6 hours) to indicate a stop to the user, are you interested?  Best regards

mafia97231 · Answer

I’m game XD

PapyLuc51 · Answer

Select Z2:BC12. Open the conditional formatting / Add a rule (the range is already set). In the formatting rule, expand the selection window and click at the bottom "Custom formula is" and enter the formula =COUNTA(B2:Y2)=24  Choose black font and black background then OK  You need to move this rule to the top of all others by clicking on the 4 vertical dots to the left of the color box and dragging it up.  I have applied it to your table. See the row Myriam, the black cell indicates it's time to stop working at the end of the last 15-minute segment recorded (24 consecutive non-empty cells)  In the row Johanna, there are 4 non-empty cells followed by 4 empty cells then 24 non-empty cells, and the next cell is black.  Best regards

mafia97231 · Answer

Once again, thank you very much Grandpa Luc!  I’m adopting your method!  I think we could achieve a better result with a script.  If anyone knows how to do it, feel free to speak up ^^  Thanks again.

Google Sheets: Last Non-Empty Cell - First Non-Empty Cell

6 answers

Pdf file

Iptv iron pro cut off

Fraudsters are now using your real iban in their phishing emails to rob you.

List numbering style

Set up a network

Unable to add an email account in outlook.

Sony wh-1000xm5 not turning on after getting wet

Google earth pro route not working at all

Texture issues in enshrouded

Usb port → usb serial