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 shifts are defined as 1st Shift: 12:00 AM to 8:00 AM, 2nd Shift: 8:00 AM to 4:00 PM, and 3rd Shift: 4:00 PM to 12:00 AM.” I also want to represent that result in a pie chart. Please note that column F is the result in another spreadsheet from a report that I do not have the option to change or separate.

 

In other words, how many times did Chaplains file a Progress Note during the 1st Shift: between 12:00 AM to 8:00 AM, the 2nd Shift: between 8:00 AM to 4:00 PM, and the 3rd Shift: between 4:00 PM to 12:00 AM.?

excel

2 Replies

  • 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:

     

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    May 04, 2023

    Richard_JamesI hope I have understood the question correctly. Please try the following formulas for Shift 1-3:

    =SUMPRODUCT(--(MOD(F2:F427;1)<=1/3))
=SUMPRODUCT(--(MOD(F2:F427;1)>=1/3);--(MOD(F2:F427;1)<=2/3))
=SUMPRODUCT(--(MOD(F2:F427;1)>=2/3))

     

Resources