by row formula

Brass Contributor


I needed a formula that would return a yes or no result by row in column A, if the dates in column that correspond with the patient IDs in column C are within 30 days of each other. some background, column C is patient ID, there are at least 2 or more of each and within those I need to see if the dates that correspond to those Patient IDs are within 30 days of each other. 

Thank you






8 Replies


Isn't this the same question as in this discussion?

Formula for excel - Microsoft Community Hub

If it's different can you add the expected results in your screenshot? Thank you.

Similar, when I went back to look at the formula after I thought it was fixed, I Realized the previous formula was copied in all the cells, so I still had the spill error, wasnt sure once they closed out the discussion if I could go back and ask the question again so I posted it as a new question. and I will have to remove the previous one.
It looks like your data is tabled. BYROW will Spill and Tables are scalar-based. If you convert the table to a range the error may go away.
it still didnt work now some cells say CALC error on the cells
Are you able to share an anonmyized copy of the workbook?


As variant

=LET(dates, SORT(FILTER([DtCreate],[PatientId]=[@PatientId])),
    IF( ROWS(dates) = 1, "One date",
    IF( MIN( DROP(dates,1) - DROP(dates, -1) ) < 30,
     "Yes", "No" ) ) )


My rendition:

    GetDates, LAMBDA(row,
            dates, FILTER(DtCreate, PatientID = row, 0),
            diff, DROP(dates, 1) - DROP(dates, -1),
            IFERROR(IF(MIN(diff) <= 28, "Yes", ""), "")
    BYROW(PatientID, GetDates)