Hi
I have two dates in the format DD.MM.YYYY in the first two cells and I want to get the time difference in the third cell in the format YY-MM-DD.
E.g.:
1st cell: 1.9.2010,
2nd cell: 31.12.2011,
3rd cell (result): 01-04-00.
So the result means: the time difference between the given dates is 1 year, 4 months and 0 days.
How to get that result in Calc?
Thanks a lot in advance
If 1st cell is A1 and 2bd cell is B1 then
=TEXT(DATEDIF(A1;B1+1;"y"); "00") & "-" & TEXT(DATEDIF(A1;B1+1;"ym");"00") & "-" & TEXT(DATEDIF(A1;B1+1;"md");"00")
Update Yes, we can record a period for a whole set of ranges. The formula will be very long and difficult to understand.
For example, if your start and end dates are in columns B and C, as in the attached spreadsheet, then the formula would be:
=TEXT(DATEDIF(MIN(B2:C32);MIN(B2:C32)+SUMPRODUCT(C2:C32-B2:B32+1)+1;"y"); "00") & "-" & TEXT(DATEDIF(MIN(B2:C32);MIN(B2:C32)+SUMPRODUCT(C2:C32-B2:B32+1)+1;"ym");"00") & "-" &
TEXT(DATEDIF(MIN(B2:C32);MIN(B2:C32)+SUMPRODUCT(C2:C32-B2:B32+1)+1;"md");"00")
In this spreadsheet, I tried to describe in detail the sequence of reasoning - maybe I was mistaken somewhere, an analysis of the actions will allow you to find the error. DateIntervalsInYMD.ods
Great, it works! JohnSUN, super thanks! 
Can I expand my question? 
If I have the results in a column, how to sum all the results so that I get the total in the same format YY-MM-DD?
No, that doesn’t work. Even if you take separate values for years, months and days in three different columns, you will not be able to accurately calculate the amount. For full years there will be no problems. With months, problems will begin when there are more than 12. And with days after the amount exceeds 28, you will not do anything.
Or you will be satisfied with the terrible result like as 14-32-278?
The result like 14-32-278 would be acceptable as it allows manual recalculation of days into months and months into years. Of course this method wouldn’t be 100% accurate due to different number of days in a month (28-31), but it is still better than nothing.
In effect, you calculate the difference between date “0” and date “8824 days” (B52 in @JohnSUN’s sample). This may be done using this formula derived from @JohnSUN’s initial one:
=TEXT(DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"y"); "00") & "-" &
TEXT(DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"ym");"00") & "-" &
TEXT(DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"md");"00")@mikekaganski Yes you are right! Due to this improvement, the formula becomes almost a third shorter.
I am still not sure of the correctness of the calculation. Will the month of start strongly affect the result? What happens if all the intervals are about 6 months old and they all start in January (include February)? How will the result change if all these periods begin in March (without February)?
By the way, the formula can be made even shorter:
=TEXT(DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"y")*10000+DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"ym")*100+DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"md");"00-00-00")Wrt formula correctness - I doubt such calculation usefulness (although user might imagine they get some value from that). Even for a single range, such representation makes little sense (e.g., for a range from Jan 16th till Feb 14th, should it be 29 days (as starting in January, which has more days) or 1 month (+1 day optionally), since it ends in February?). But since we have DATEDIF with its own definition, and people find it useful to use some fake numbers, then why not 
Thanks both @JohnSUN and @mikekaganski, good effort! I’ve made a quick check and found a mistake:
While the formulas =SUMPRODUCT(C3-B3+1) and =TEXT(DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"y")*10000+DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"ym")*100+DATEDIF(0;SUMPRODUCT(C2:C32-B2:B32+1);"md");"00-00-00")consider different durations of months and leap years (28, 29, 30 and 31 days), the formula =TEXT(DATEDIF(A1;B1+1;"y"); "00") & "-" & TEXT(DATEDIF(A1;B1+1;"ym");"00") & "-" & TEXT(DATEDIF(A1;B1+1;"md");"00") does not do that. Therefore when a time interval starts in the middle of the month and it ends in the next month, the result is not correct, see the cells D4, D6, D8 and D10. Which means that the result for a specific time interval (YY-MM-DD) may not be correct, while the total sum (YY-MM-DD) of all the given intervals is accurate.