• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

doctor report

Carmen Cruz

Carmen Cruz

New Member
I need to run a report that will show if doctors completed readings results with-in 2 business days from their due date and determine if they were at least 80% completed 2 days from the due date. Can someone please help me figure this out please?

The spreadsheet is attached, the Data sheet has the dates when the reports were read and the initials of the doc who read them. In the Report sheet is the weeks that each doc was scheduled to read the reports, when a doctor could not read during his week another doc would read that is what the other initial are for, to see who else read during that week.
 

Attachments

  • PFT Report.xlsx
    95.7 KB · Views: 9
So am I in the right track?
=if dates between "start date" and "end date" did "scheduled dr" read pft within "date read" which has to be within 2 business days

I need to know how to put it in a formula and if this concept is not correct please teach me...
 
It looks like you're really quite close to where you want to be. The big thing is to understand what you have and know where you want to go.

Your table was calculating the number of reports completed, but not which doctor actually reviewed the report. If I'm understanding correctly, you want to know if the scheduled doctor reviewed 80% of the reports for that week. To do that, you'd need the total number of reports and the number of reports reviewed by the appropriate doctor. To show these steps, I added three columns to your table. If you wanted, the functions could be combined into one column.

Just remember, you're getting answers from random volunteers here. People may take a bit to get back, but there are good answers here.
 

Attachments

  • PFT Report.xlsx
    103.5 KB · Views: 4
Thank you so much for your response, and I understand about being volunteer and believe me for someone like me it is very much appreciated!! I was looking the amount of people who viewed the post and the file that made me wonder if I was even making sense, and if that is why no one was responding.

Thank you so much for your respond!!!

Yes, this is correct, however I need it to look at the "D.O.S" and see if the "Date Read" fell within the 2 business day of "Start Date" and "End Date", is that possible? did I explain correct?
 
Maybe if we look at it a little differently it'll be clearer to me. I'm assuming DOS is Date of Service. How many business days after DOS would the report need to read by? For example, the first line on the Data tab has DOS as 01/05/16 and Date Read is 01/14/16. So, DOS occurred during the first week on the Report tab, but the report wasn't read until the last day of the next week. Also, 14Jan17 was a Saturday, not a normal business day. How would this line be rated and why?
 
If the D.O.S was within that ending week of 1/14/16, so if the ending date was 1/14, the doctor has until 1/17 which is the 2 business day of the due date. And 14Jan17 is the read date but the D.O.S. is on 27Dec16. The date range to read is the date range for the D.O.S, so all of the D.O.S that fall within 1/1/16 and 1/7/16 has to be read no more than 2 business days after the due date. If there were 10 records that fell within 1/1/16 and 1/7/16 and only 5 were read before the 2 business days then he would be at 50%.

Does this explain it ok?
 
Well, you're awfully close. So any record with a DOS within a given week has until the End Date + 2 to be read? We've got the total assigned in a given week in the Assigned column. I've put a new equation in Col H. This is checking for reports assigned during the week and if they were completed within 2 calendar days of the end date. Is this closer to what you wanted?
 

Attachments

  • PFT Report.xlsx
    102.9 KB · Views: 1
So Col H has the total of D.O.S that was read by the due date plus 2? I can just add a Col next to Col H with the percentage of the total from Col H? I don't need Col F or G? just one more thing, is that +2 workdays?

I think you got it!!!!!!! :)
 
This should then put it into a percentage of the reports assigned that week. I also deleted Cols F&G. You'll notice some minor cell reference changes in Cols G:O, too. I changed the Col A&B refs to structured table references so they were easier to copy.

Oh, it's +2 calendar days. Workdays is harder due to holidays, but you could be awfully close by changing the +2 to +3 since the end date is Saturday. This changes the completed day to the next Tuesday.
 

Attachments

  • PFT Report.xlsx
    101.6 KB · Views: 3
Last edited:
oh ok, that would work!!! Great! Thank you so so much for your time and expertise, you was amazing and I so appreciate what you do!! :) :)
 
Last edited:
Well, the week ending 28Jan17 would have a completion requirement of 2 work days or 31Jan17. The example you've sited had completion dates of 01Feb17, which is one day out of the range.

However, the easy general fix is to change the +2 to a +3, which will change the day of the week for completion from Monday to Tuesday. Or, I've updated the equation with the WORKDAY function in the attached file. You could add a list of holidays for that function to use, if you want.
 

Attachments

  • PFT Report.xlsx
    101.6 KB · Views: 5
Not at all. Most of it is just what you had originally, really. I'm assuming you understood the original CountIfs functions. If not, just let me know and I can go over the range and criteria logic.

=iferror(function,"") checks for a calculation error. If there is no error, you get the function output. If an error is found, a null string is output. You can change the null to whatever you'd like. This is a handy error checking function.

The first CountIfs() counts how many records with DOS within the Start/End range were read before the end date + 2 working days. Note this doesn't check if the assigned doctor read the record, just if it was read. If you'd like the doctor assigned for that week specified, just add ",Table1[Dr. who Read],[@[Assigned Week]]" to the end of this CountIfs.

The denominator CountIfs counts the total number of records with DOS within the Start/End range. There's also a "*100" to convert to percentage.
 
ok, splendit, that was perfect! I wasn't to sure about the =iferror and null string
how it came to play. Your awesome!!! thank you so much!!
 
I'm just a little confused as to where after countif to add ",Table1[Dr. who Read],[@[Assigned Week]]"

=IFERROR(COUNTIFS(Table1[D.O.S.],">="&[@[Starting Week]],Table1[D.O.S.],"<="&[@[Ending Date]],Table1[Date Read],"<="&WORKDAY([@[Ending Date]],2))/COUNTIFS(Table1[D.O.S.],">="&[@[Starting Week]],Table1[D.O.S.],"<="&[@[Ending Date]])*100,"")
 
Right here:

=IFERROR(COUNTIFS(Table1[D.O.S.],">="&[@[Starting Week]],Table1[D.O.S.],"<="&[@[Ending Date]],Table1[Date Read],"<="&WORKDAY([@[Ending Date]],2),Table1[Dr. who Read],[@[Assigned Week]])/COUNTIFS(Table1[D.O.S.],">="&[@[Starting Week]],Table1[D.O.S.],"<="&[@[Ending Date]])*100,"")
 
Oh, ok... I did that but put it after the number 2 and before the parentheses and it was not working... Thank you!
 
You must log in or register to reply here.
Back
Top