Clearing contents in an adjacent cell based on the value in the cell to the right or left - Help Please

[Joshuab712]

Hello all, I am in need of a solution. I am running a report on the total amount of units available during a 24 hour period. The output looks like:

Unit	0:00	:15	:30	:45	1:00	:152	:303	:454	2:00	:155	:306	:457	3:00	:158	:309	:4510	4:00	:1511	:3012	:4513	5:00	:1514	:3015	:4516	6:00	:1517	:3018	:4519	7:00	:1520	:3021	:4522	8:00	:1523	:3024	:4525	9:00	:1526	:3027	:4528	10:00	:1529	:3030	:4531	11:00	:1532	:3033	:4534	12:00	:1535	:3036	:4537	13:00	:1538	:3039	:4540	14:00	:1541	:3042	:4543	15:00	:1544	:3045	:4546	16:00	:1547	:3048	:4549	17:00	:1550	:3051	:4552	18:00	:1553	:3054	:4555	19:00	:1556	:3057	:4558	20:00	:1559	:3060	:4561	21:00	:1562	:3063	:4564	22:00	:1565	:3066	:4567	23:00	:1568	:3069	:4570
B13-01	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-02	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-03	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-06	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-07	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-08	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-09	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-11	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B10-12	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

The furthest left column represents a shift name. The top row represents 24 hours of time in 15 min increments. I have a simple sum formula at the bottom which adds each column and outputs a total "Shifts working" per every 15 mins. I.E. B10-12 (bottom row) works 07:00 - 16:45, with the "1" representing the time at work, "0" representing the time not working.

The issue is that the output is 15 mins over on the front and back end. I.E. if the shift is scheduled for 10 hours I can't count the first 15 mins and the last 15 mins per shift. With the example above I would need B10-12 to actually show that it is worked from 07:15 - 16:30. I need to remove the "1" at the beginning and end of each sequence of "1's". This would give me an accurate amount of total units available per 15 min time frame. I am having a significant amount of difficulty solving the problem and any help you all can provide will be greatly appreciated. Thank you all for your time and considerations.

 

[Jamie McMillan]

Hello all, I am in need of a solution. I am running a report on the total amount of units available during a 24 hour period. The output looks like:

Unit	0:00	:15	:30	:45	1:00	:152	:303	:454	2:00	:155	:306	:457	3:00	:158	:309	:4510	4:00	:1511	:3012	:4513	5:00	:1514	:3015	:4516	6:00	:1517	:3018	:4519	7:00	:1520	:3021	:4522	8:00	:1523	:3024	:4525	9:00	:1526	:3027	:4528	10:00	:1529	:3030	:4531	11:00	:1532	:3033	:4534	12:00	:1535	:3036	:4537	13:00	:1538	:3039	:4540	14:00	:1541	:3042	:4543	15:00	:1544	:3045	:4546	16:00	:1547	:3048	:4549	17:00	:1550	:3051	:4552	18:00	:1553	:3054	:4555	19:00	:1556	:3057	:4558	20:00	:1559	:3060	:4561	21:00	:1562	:3063	:4564	22:00	:1565	:3066	:4567	23:00	:1568	:3069	:4570
B13-01	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-02	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-03	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-06	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-07	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-08	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-09	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B13-11	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B10-12	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

The furthest left column represents a shift name. The top row represents 24 hours of time in 15 min increments. I have a simple sum formula at the bottom which adds each column and outputs a total "Shifts working" per every 15 mins. I.E. B10-12 (bottom row) works 07:00 - 16:45, with the "1" representing the time at work, "0" representing the time not working.

The issue is that the output is 15 mins over on the front and back end. I.E. if the shift is scheduled for 10 hours I can't count the first 15 mins and the last 15 mins per shift. With the example above I would need B10-12 to actually show that it is worked from 07:15 - 16:30. I need to remove the "1" at the beginning and end of each sequence of "1's". This would give me an accurate amount of total units available per 15 min time frame. I am having a significant amount of difficulty solving the problem and any help you all can provide will be greatly appreciated. Thank you all for your time and considerations.

Hello,

You could write a formula in a cell: next to/below the output total.

If counting how many 1’s then

=SUM(B1:B12)-2

{You would get some minus values: so

=IF(SUM(B4:B15)=0,0,SUM(B1:B12)-2)

For counting the total time just add *15 at the end to give tou the total minutes; or *15/60 to give you the total hours worked.

 

[Joshuab712]

Hello,

You could write a formula in a cell: next to/below the output total.

If counting how many 1’s then

=SUM(B1:B12)-2

{You would get some minus values: so

=IF(SUM(B4:B15)=0,0,SUM(B1:B12)-2)

For counting the total time just add *15 at the end to give tou the total minutes; or *15/60 to give you the total hours worked.

Thank you for responding, I was hoping for a VBA formula that would remove the "1"'s at the end of the series that I could copy and paste as I run new reports, since this won't be a static sheet.

 

[Jamie McMillan]

Thank you for responding, I was hoping for a VBA formula that would remove the "1"'s at the end of the series that I could copy and paste as I run new reports, since this won't be a static sheet.

Hello,

I put a list of, 0 & 1s, in row 1. a1 to p1 a group of is in the middle then 0s on either end.

Dim i As Integer
Dim rng As Range, cell As Range
Set rng = Range("a1:p1")   'the range of your last row
i = 1
Do Until Range("a2").Value = 1     'A2 is any cell unused in your workbook
If Cells(1, i).Value = 1 Then
    Cells(1, i).Value = 0
        Range("a2").Value = Range("a2").Value + 1
        End If
        i = i + 1
Loop          
           ' now you do the opposite
i = 16      '16 is the last column in the range - P1 is the 16th column
Do Until Range("a3").Value = 1       'A2 is any cell unused in your workbook
If Cells(1, i).Value = 1 Then
    Cells(1, i).Value = 0
        Range("a3").Value = Range("a3").Value + 1
        End If
        i = i - 1
Loop
Range("a2").Value = 0
Range("a3").Value = 0          'clears cells A2 & A3 for the next time

Hope this solves your problem.

Jamie