/

How to Calculate Date Difference in LibreOffice Calc

42537 views
1

Having trouble with date calculations in LibreOffice Calc? In this guide, you will learn how to calculate the types of date difference in LibreOffice Calc.

Calculate Date Difference

Its always better to use a function to calculate the date difference. DATEDIF the function does just that. This function returns the number of whole days, months, years between two given start and end dates.

Syntax

=DATEDIF(Start date; End date; Interval)
  • Start date and End date are the date range for your calculation.
  • Interval is a string and accepted values are below.
Interval ValuesDescription
dNumber of whole days between Start date and End date.
mNumber of whole months between Start date and End date.
yNumber of whole years between Start date and End date.
ymNumber of whole months when subtracting years from the difference of Start date and End date.
mdNumber of whole days when subtracting years and months from the difference of Start date and End date.
ydNumber of whole days when subtracting years from the difference of Start date and End date.

Examples

Number of days between two dates

If you want to calculate the number of days between two dates, use the DATEDIF function with interval "d".

=DATEDIF(A2,B2,"d")
Number-of-days
Number-of-days

Number of months between two dates

In the above example, replace the interval with m to find out the number of months between two dates.

=DATEDIF(A2,B2,"m")

Number of years between two dates

Likewise, use the interval value y to calculate the number of years between two dates.

=DATEDIF(A2,B2,"y")

Number of months ignoring years

If you want to calculate the number of months between two dates ignoring the years, then use ym in the interval parameter.

Number of days ignoring months

Similarly to calculate the number of days between two dates ignoring the months, then use ym in the interval parameter.

Number of days ignoring years

Likewise, if you want to calculate the number of days between two dates ignoring the years, then use yd in the interval parameter.

Number-of-days-ignoring-years
Number-of-days-ignoring-years

Age calculation using birthday

You can also calculate age using the birthday of a person using the below formula by calculating DATEDIF between birthday and today’s date (via TODAY() function).

Age-calculation-using-Birthday
Age-calculation-using-Birthday

Usage Notes

If you do a DATEDIF between two dates where your end date is less than your start date then you would get Err:502. So, in those cases, you can use =MONTH() or DAY() function and do subtraction between them.

Reference

Join our Telegram channel and stay informed on the move.

arindam

Creator of libreofficehelp.com. The aim is to help as many people as possible with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Setiadjit Djoko Santoso DVM

How to link a cell from diffrence worksheet or file; Sometime it doesn’t working;
Thank your
@setiadjit

%d bloggers like this: