Creating a new column containing time in months based on specific conditions

Question

I am trying to prepare an Excel file for analysis, but I can't seem to figure out how to solve the issue below.

The data involves a study in which participatns are followed for 5 years (even though longer follow-up is available). During these 5 years, it is recorded whether a specific event happens or not.

I have the following data available (an example):

ID	Entry date	Event
1	13-02-2020	27-06-2023
2	06-07-2021
3	12-01-2020	13-10-2022
4	09-08-2020	26-02-2027
5	17-05-2021

"Entry date" is time of entry to the study, and "Event" is the date of the event, if the event happened. If there is a blank cell, it means the event did not happen.

I am now trying to create a new column called "Time", which contains the time in months from the "Entry date" to the date of the "Event", if the event happened within 5 years; or, if the event did not happen at all or happened after 5 years, the time in months which would equal the entry date + 5 years (thus indicating that the participant was followed for 5 years and then censored).

I hope this makes sense. Thanks in advance.

Answer 1

Depending on your definition of a "month", perhaps:

=IFERROR(MIN(DATEDIF(B2,C2,"m"),60),60)

Answer 2

If you do not need an absolutely exact "month", then use e.g.

=ROUND( (C2-B2) / (365.25/12) ,0)

There is an average of 365.25 days in a year, and 12 months in the same year.