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.
Table of Contents
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 Values | Description |
|---|---|
| d | Number of whole days between Start date and End date. |
| m | Number of whole months between Start date and End date. |
| y | Number of whole years between Start date and End date. |
| ym | Number of whole months when subtracting years from the difference of Start date and End date. |
| md | Number of whole days when subtracting years and months from the difference of Start date and End date. |
| yd | Number 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 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.
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).
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.
I hope this clarifies the basics of date difference in LibreOffice Calc. Do let me know in the comments below for any issues.
How to link a cell from diffrence worksheet or file; Sometime it doesn’t working;
Thank your
@setiadjit
Come on come on what date format is it is it month-day-year or dat-month-year
this is a mistake how do I delete it why put up the faulty one and the corrected version
And the real reason why they are working on Artificial Intelligence (AI) is statistics has realized that the world is going stupid.
for example ever gone to a internet search engine and put in a query, then the search engine asks “did you mean ……….” but there is no “YES” “NO” button to give a answer.
How kinky?
Now we have wasted all our money on Widescreen TV and can not afford a holiday, a lot of news clips are shown in narrow vision from cell phones, it looks like the TV news producers have lost their marbles.
And sad as it may be, that is just the tit of the iceberg.
come on come on what date format is it day-month-year or month-day-year