The DATEDIF function returns the number of years, months, or days between two dates, and can be used to calculate the years of employment, contract length, loan duration, time to birthday, and more.
I. Intro
Arguments
DATEDIF(start date, end date, unit)
- Start date: The starting date of the period, which can be a date field, or a number or formulas field in date format
- End date: The ending date of the period, which can be a date field, or a number or formulas field in date format
- Unit: Abbreviations of time units. Valid values include: "Y" (whole years), "M" (whole months), "D" (days), "MD" (days remaining, ignoring the months of the start and end dates.), "YM" (months remaining, ignoring the years of the start and end dates.), "YD" (days remaining, ignoring the years of the start and end dates.)
Notes:
- "YD" ignores the year and only calculates the number of days between two dates. For example, September 5, 2015 to August 31, 2021 is 5 years and 361 days apart. If "YD" is used as the unit, the result will be 361, which means 361 days.
- The start date must be after 1900, and the end date must be later than the start date.
II. Steps
- Open the base.
- In grid view, click the + icon next to the rightmost field.
- Select Formula as the field type.
- Click Edit Formula and enter the DATEDIF function. Enter the start date, end date, and unit as required, and click Confirm.
- In the Field format section, select a format as needed and click Confirm.
Note: You may get an error message if the incorrect format is selected. Try selecting a different format or changing the arguments in the DATEDIF function to troubleshoot.
250px|700px|reset
III. Use case
- Calculate remaining days of contracts
Scenario: When dealing with multiple contracts, it is imperative to keep track of the days remaining in the contract to prepare for renewal or negotiation.
Formula: DATEDIF(start_date,end_date,"D")
Explanation: The formula calculates the number of days from today to the contract's expiry date. Here, the TODAY() function returns today's date.
250px|700px|reset
- Set up birthday reminders
Scenario: The company can set up a countdown reminder for employees' birthdays, enhancing employee satisfaction and loyalty.
Formula: CONCATENATE(365-DATEDIF(date_of_birth,TODAY(),"YD")," days until birthday")
Explanation: The DATEDIF function is used to calculate the number of days until the next birthday by calculating the number of days that have passed since the last birthday and then subtracting that from the number of days in the year. The CONCATENATE function is used to combine "There are" with the number of days to create a countdown to the employee's birthday.
- To get a list of employees who have birthdays this week at the beginning of each week, you can use the following formula:
Formula: IF(DATEDIF(date_of_birth-7,TODAY(),"YD")<7,CONCATENATE(7-DATEDIF(date_of_birth-7,TODAY(),"MD")," days until ",name,"'s birthday"),"")
Explanation: The DATEDIF function is nested within the IF function, which performs the following: If the birth date is less than 7 days from the today, return "[number] days until birthday", otherwise return null.
250px|700px|reset
- Calculate employment tenure and salary
Scenario: Calculate employment tenure and salary, where more than six months but less than a year are counted as one year.
Formulas: IF(INT(RIGHT(ROUNDDOWN(DATEDIF(entry_date,TODAY(),"M")/12,1),1))<5,INT(DATEDIF(entry_date,TODAY(),"M")/12)+0.5,INT(DATEDIF(entry_date,TODAY(),"M")/12)+1)
Explanation: The DATEDIF function calculates how long the employee has been with the company in years. ROUNDDOWN and RIGHT functions are used to round the results to 1 decimal place. The IF function makes it so that if the tenth place is 5 or greater, then the result is rounded up, making it a full year of employment.
Note: To calculate the salary, use the working years to determine seniority and multiple that by the pay raise for that seniority, and then add the basic salary.
250px|700px|reset
IV. FAQs