Forum Discussion

Richard_James's avatar
Richard_James
Brass Contributor
May 04, 2023

Formula Help with Counting Chaplain Visits Over Three Shifts Per Day

The Date and Time column E is the record of the Progress Note. My question is, ”What is the formula to calculate in Excel version 16.65 to get the number of Chaplain Visits occurrences Per Shift if s...
excel
Patrick2788's avatar
Patrick2788
Silver Contributor
May 04, 2023

Richard_James 

 

Here's my solution:

 

'Shifts

=LAMBDA(a,v,LET(
    filtered, FILTER(FileTime, Dates = v),
    freq, TOROW(FREQUENCY(filtered, v + BIN)),
    First2Shifts, TAKE(freq, , 2),
    Shift3, SUM(TAKE(freq, , -2)),
    VSTACK(a, HSTACK(v, First2Shifts, Shift3))
))

'Sheet level formula
=REDUCE(Header,UNIQUE(Dates),Shifts)

 

A glimpse of some of the results:

 

Resources