How to Calculate Date Difference in LibreOffice Calc?
My cell format looks ok, but result is not correct (right result should be several hours)…
Calculate Date Difference in LibreOffice Calc.ods (13.4 KB)
You need to format cell B3, currently it is showing the correct answer but as a portion of a day.
I don’t know what time periods you are intending to calculate but for the current example format the cell as [HH]:MM:SS.000
1 Like
For B3 I make format YYYY-MM-DD HH:MM:SS.000 and then I get crazy result 1899-12-30 10:50:04.935 but if I set format HH:MM:SS then I get correct result 10:50:04
Why I can’t use format with year?
Because there’s no “years” in time difference (duration). Year is not a specific time length.
is also not a correct format for durations. You already were told to use formats with square brackets, that allow to show more than 24 hours.
1 Like
Your date-times are text, possibly from a faulty csv import.
Turn on View>Value Highlighting
Select the date-time values.
Call Edit>Find&Replace
Check “Current Selection Only”
Check “Regular Expressions”
Search: .+
Replace: &
[Replace All]
Notice blue font of the numeric values due to value highlighting. You can format the numeric values any way you like without changing the actual values.
All date-time calculations work as expected.
=A2-A1 gets the difference in days.
The value in [B3] is correkt as a fraction of one day, so you get the number of hours by the multiplication that decimal value with 24 without formating as a DATUM. To extract the minutes and seconds you need a complex formula cause of 60m÷1h and 3600s÷1h only for show not to calculate by.
like =MINUTE(B3) 
minute != minutes
@karolus please try to be specific and descriptive. “Extract” minutes is not “convert to” minutes - if you consider “extract” as “get minutes as shown in the HH:MM:SS notation”, and “convert” as “get as shown in [MM] notation”… but anyway, I don’t want to solve riddles.
fair enough, if @koyotak means
the minutes-part, but from the previous:
… I wrongly assumed, »its about calculating the whole number of minutes« … ( IMHO which isn’t complex but simply multiplying by 1440 )
I have difficulty calculating the difference in days between 2 dates, because unfortunately each month has a different number of days, from 28 to 31. Please play around with the following file:
Calculate Date Difference_trial-001_022619.ods (23.6 KB)
=A4-A3 with no special number format gives the difference in days.
DAY(B4)-DAY(B3) calculates 20-20 because both figures share the same day portion.
2025-02-20 02:01:26.302
2026-03-20 00:51:31.237
Its simply:
=ROUNDDOWN(A4-A3)
@koyotak and since you also try to find the difference in months and years - see DATEDIF function.
2 Likes