SOLVED

30 day Readmission: Date difference between first discharge and readmission

%3CLINGO-SUB%20id%3D%22lingo-sub-2778309%22%20slang%3D%22en-US%22%3E30%20day%20Readmission%3A%20Date%20difference%20between%20first%20discharge%20and%20readmission%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778309%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20want%20to%20track%2030-day%20readmission%20cases%20and%20want%20to%20calculate%20the%20day%20difference%20between%20the%20first%20discharge%20date%20(end-date)%20and%20readmission%20date%20(start%20date).%26nbsp%3B%20In%20the%20dataset%2C%20I%20have%20the%20following%20variables%3C%2FP%3E%3CP%3EA%20-unique%20ID%20code%20for%20each%20person%20(you%20will%20meet%20this%20code%20as%20many%20times%20he%2Fshe%20was%20admitted)%3B%3C%2FP%3E%3CP%3EB%20-%20Case%20ID%20which%20is%20a%20unique%20code%20for%20each%20admission%20(there%20could%20be%20cases%20when%20it%20is%20the%20same%20when%20referral%20occurred%20but%20such%20example%20is%20not%20present%20in%20this%20sample%20dataset)%3C%2FP%3E%3CP%3EC-Recieve%20date%3C%2FP%3E%3CP%3ED-%20Discharge%20date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20work%20both%20on%20Windows%2010%20and%20MacOS%20big%20sure%2011.6.%20Excel-%2016.53%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2778309%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778402%22%20slang%3D%22en-US%22%3ERe%3A%2030%20day%20Readmission%3A%20Date%20difference%20between%20first%20discharge%20and%20readmission%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164514%22%20target%3D%22_blank%22%3E%40Marit585%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%0A%3CP%3EThe%20values%20in%20columns%20C%20and%20D%20were%20text%20values%2C%20not%20dates.%20I%20used%20Data%20%26gt%3B%20Text%20to%20Columns%20to%20convert%20them%20to%20dates.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778409%22%20slang%3D%22en-US%22%3ERe%3A%2030%20day%20Readmission%3A%20Date%20difference%20between%20first%20discharge%20and%20readmission%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778409%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164514%22%20target%3D%22_blank%22%3E%40Marit585%3C%2FA%3E%26nbsp%3B%20So%20i%20formatted%20the%20data%20as%20a%20table%20and%20used%20'Text%20to%20Columns'%20to%20force%20the%20dates%20in%20the%20date%20columns%20to%20format%20as%20dates%20and%20not%20text%20(maybe%20your%20version%20recognizes%20them%20as%20dates%20already).%26nbsp%3B%20Then%20I%20created%20a%20column%20that%20calculates%20the%20Time%20since%20last%20Dischaarge%20using%20a%20formula%20that%20will%20filter%20all%20the%20discharge%20dates%20based%20on%20uniqueID%2C%20sort%20them%20and%20find%20the%20closest%20that%20is%20less%20than%20the%20receive%20date%20and%20take%20the%20difference.%26nbsp%3B%20Then%20I%20created%20a%20column%20that%20gives%20a%20T%2FF%20based%20on%20it%20being%20%26lt%3B%3D30.%26nbsp%3B%20It%20could%20be%20done%20in%201%20column%20but%20I%20think%20that%20Time%20since%20last%20Discharge%20could%20be%20valuable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I want to track 30-day readmission cases and want to calculate the day difference between the first discharge date (end-date) and readmission date (start date).  In the dataset, I have the following variables

A -unique ID code for each person (you will meet this code as many times he/she was admitted);

B - Case ID which is a unique code for each admission (there could be cases when it is the same when referral occurred but such example is not present in this sample dataset)

C-Recieve date

D- Discharge date

 

I work both on Windows 10 and MacOS big sure 11.6. Excel- 16.53 

3 Replies

@Marit585 

See the attached version.

The values in columns C and D were text values, not dates. I used Data > Text to Columns to convert them to dates.

best response confirmed by Marit585 (New Contributor)
Solution

@Marit585  So i formatted the data as a table and used 'Text to Columns' to force the dates in the date columns to format as dates and not text (maybe your version recognizes them as dates already).  Then I created a column that calculates the Time since last Dischaarge using a formula that will filter all the discharge dates based on uniqueID, sort them and find the closest that is less than the receive date and take the difference.  Then I created a column that gives a T/F based on it being <=30.  It could be done in 1 column but I think that Time since last Discharge could be valuable.