Greetings
Hope all is well,
I was hoping someone could assist me with this.
Sintek made sure that my next post includes as much details as possible with the best possible explanation.
Hope the below makes sense.
Attached is "Aging Sheet"
There is currently 7 Columns & 10 Rows in the Table ("Table1") from A1:G10 (Columns will be added daily and thus why a code is used to get last row in table
A1:G1 is Table1 Headers ( A1 = Reference for A:A, B:G Dates )
My reference Column is A
Each Row from 2 to 10 consist of text values "Online","Offline" and "-"
There are 2 Columns from I:H wich is used for "Status"(I1) & "Days"(J1)
Below is the code Sintek helped me with to determine the Status based on different Criterias
Now I now need to get the "Days" column to be populated as per attached.Please Login or Register to view this content.
I have achieved this with normal formulas but is not consistent.
I am trying to count the days the "Status" has been the same.Please Login or Register to view this content.
Each Row in (J2 to LastRow) count the columns from right in Table1 and use that columns header in a Networkdays Formula to determine what the Duration of "Status" is.
You will notice that the Formula does not look at the current Day due to:
me not being able to iterate what's in my head.
If G2 = I2 and F2 = I2 then
count from G2 to where *column <> I2(Backwards / Right to Left) -- Basically if the column does not match the current Status then it means it only needs to count to that column as the status for that Day(Column) is different from the "Status" in I2.
If G2 = I2 and F2 <> I2 Then
Count from F2 to where *column <> I2(Backwards / Right to Left) -- Basiccaly if the Last Column does Match I2 but Last Colum -1 Does not match I2 then count from the cell which does not match(F2 in this case)
Kind Regards,
thank you for the support.
Bookmarks