Forum Discussion
Ahmed_Ben_2021
Oct 05, 2021Copper Contributor
Calculating the number of months between two dates for survival analysis
Device: Mac
Excel product: Microsoft Excel for Mac / Version 16.53 (21091200)
I am conducting survival analysis, and I want to calculate the survival time between the start and first events.
I found many excel functions; however, there is a mild difference in the results, i.e. Fractions.
Here is the function I found:
1- =DAYS(first_event_date,Start_date)/30
2- =DATEDIF(Start_date,first_event_date,"M")
3- =YEARFRAC(Start_date,first_event_date)*12
Here is my data; it is just one event I can apply to the rest.
Starting date: 2005-10-14
First event date: 2007-05-30
Results according to the previous functions:
1- 19.76
2- 19
3- 19.53
My question is, which function I should use to get the most accurate answer.
Thank you,
A
Actually, how do you get the table that excludes blanks? I see Q5 just says '=LET(', but what does this mean and how does this know to reference Table 1?
If you increase the size of the formula bar you'll see that in Q5 the formula is (I updated it - feel free to re-download the sample I earlier shared):
=LET( n, F5, FilterCol, LAMBDA(col, LET(values, CHOOSECOLS(Table1,col), FILTER(values,ISNUMBER(values),FALSE))), StackCol, LAMBDA(init,col, HSTACK(init, TAKE(FilterCol(col),-n))), Array, IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(Table1)), StackCol),,1),FALSE), Array )
where you can see the 2 references to Table1
If you want to make it more obvious or for easier maintenance you can use:
=LET( myTable, Table1, n, F5, FilterCol, LAMBDA(col, LET(values, CHOOSECOLS(myTable,col), FILTER(values,ISNUMBER(values),FALSE))), StackCol, LAMBDA(init,col, HSTACK(init, TAKE(FilterCol(col),-n))), Array, IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(myTable)), StackCol),,1),FALSE), Array )