Maureen's Blog

 Archive for the ‘ISP’s’ Category

My friend Greg Olson in Montana is not only a great fan of Therap but has also agreed to share some of his expertise in auditing ISP Data. We all know well enough how important it is to prove that we are providing the best services we can to the people we support and it’s devastating when we loos a piece of paper that contains all the activities an individual went on for a month. Although we now do not have to worry about that missing piece of paper we can focus more on ensuring staff remember to enter the data. Greg’s formula is a quick and easy audit which will export all the ISP data to excel and locate anything that is missing. Here ya go!

EXCEL FORMULA FOR CONDUCTING THERAP ISP DATA REVIEW

TO FIND MISSING DATA DATES

ONE OF THE THINGS YOU MAY HAVE TO PROVE TO A STATE MEDICAID AUDITOR IS WHETHER OR NOT YOU PROVIDED THE SERVICES IN A PERSON’S  MEDICAID PLAN FOR A SPECIFIC DATE OR SERIES OF DATES.

CONDUCTING TESTS LIKE THIS MANUALLY AND CHECKING DATES AGAINST THE ORIGINAL DOCUMENTATION IS EXTREMELY TIME CONSUMING AND IS PRONE TO ERRORS.

THE FORMULA AND PROCEDURE BELOW WILL FACILITATE THIS PROCESS MAKING IT RELATIVELY QUICK (2 – 3 MINUTES PER PERSON FOR ANY LENGTH DATA  COLLECTION PERIOD) TO DO SO AND PROVIDE THE EVIDENCE NEEDED.

BECAUSE OF THE REDUCTION IN TIME THIS FORMULA PROVIDES, IT IS POSSIBLE TO CONDUCT CHECKS LIKE THIS ON A REGULAR BASIS AND DEFICIENCIES CAN BE QUICKLY DETERMINED AND FIXED.

PROCEDURE

1. CONDUCT AN ISP DATA SEARCH IN THERAP FOR THE PERSON.
2. SORT THE RESULTING DATA SEARCH BY DATE AND/OR ISP NAME (click on top of appropriate column)
3. EXPORT THE DATA SEARCH TO EXCEL (bottom of the Search page)
4. CONVERT THE DATA COLLECTION DATES FROM TEXT TO DATES BY SELECTING THE COLUMN OF DATES. UNDER “DATA” IN THE EXCEL TOOLBAR, SELECT “TEXT TO COLUMNS” AND FOLLOW THE DIRECTIONS – SELECT DATES AND MDY WHEN GIVEN THAT CHOICE. THIS WILL CONVERT THE “TEXT DATES” TO FORMATTABLE DATES.
5. CHANGE THE DATES TO THE “DAY OF THE WEEK, MONTH, DATE, YEAR” FORMAT UNDER “FORMAT/CELLS”. (YOU DON’T HAVE TO DO THIS ONE BUT IT MIGHT BE USEFUL AT THE BEGINNING)
6. INSERT 2 NEW COLUMNS RIGHT OF THE DATE COLUMN. (OR USE EXISTING ADJACENT COLUMNS IF YOU HAVE NO OTHER USE FOR THEM)

ADD A RANGE OF DATES IN THE CLOSEST NEWLY CREATED COLUMN THAT COVERS THE SAME RANGE IN THE CONVERTED

1. TYPING OR COPYING THE FIRST DATE, SELECTING EDIT, FILL, SERIES, DATE AND WEEKDAYS (DAY PROGRAM) OR DAY (RESIDENTIAL).
2. IN THE FIRST CELL OF THE SECOND NEW COLUMN, WRITE THE FOLLOWING FORMULA:

THIS IS A DESCRIPTION:

=VLOOKUP(“FIRST CELL OF COMPARISON DATE (new dates) COLUMN”,”LETTER OF CONVERTED DATE COLUMN”  (therap generated dates): “LETTER OF CONVERTED DATE COLUMN” (therap generated dates),1,FALSE)

THIS IS AN EXAMPLE:

=VLOOKUP(C2,A:A,1,FALSE)

THIS FORMULA WILL COMPARE THE DATES THAT DATA WAS COLLECTED FOR THE ISP AGAINST ALL OF THE DATES IN THAT SAME PERIOD, INCLUDING HOLIDAYS AND MISSING DATES AND WILL RETURN EITHER 12:00 IF THE DATE IS FOUND OR “NA” IF THAT DATE IS MISSING.

AT THIS POINT YOU CAN QUICKLY COMPARE ALL OF THE “NA’S” TO THE DATES IN THE ADJACENT COLUMN AND DETERMINE IF THERE SHOULD HAVE BEEN DATA COLLECTED FOR THAT DATE OR NOT.

ONCE YOU HAVE BECOME PROFICIENT AT THIS PROCESS IT IS POSSIBLE TO CHECK A YEAR’S WORTH OF DATA FOR ONE PERSON IN APPROXIMATELY 2 – 3 MINUTES OR LESS.

NOTE THAT THIS PROCESS DOES NOT SPEAK TO THE QUALITY OF DATA NOR DOES IT EASILY IDENTIFY DUPLICATE ENTRIES, IT MERELY ALLOWS YOU TO CHECK AN ISP AGAINST THE DATES THAT DATA SHOULD HAVE BEEN COLLECTED.

Thank You Greg!

~Maureen