time and date difference

users
#1

hello everyone,
I am using LibreOffice Version: 4.3.0.4 and I am having issues calculating
time differences..

I would like to calculate the time difference in hours between the following
date/times:

E14 = 23/04/2015 15:30
E15 = 25/04/2015 16:20
E16 = DAYS(E15;E14)

But I get a value error so I split data:

F14 = 23/04/2015
F15 = 25/04/2015
F16 = DAYS(F15;F14) ---> Ok, I get 2, multiply x 24 = 48h and it's fine

then for the hours

G14 = 15:30
G15 = 16:20
G16 = (G15<G14)+G15-G16 (as suggested by the wiki:
https://help.libreoffice.org/Calc/Calculating_Time_Differences)

but again I have a value error..

does anyone knows how can I solve this??

#2

I am using LibreOffice Version: 4.3.0.4 and I am having issues calculating time differences. I would like to calculate the time difference in hours between the following
date/times:
E14 = 23/04/2015 15:30
E15 = 25/04/2015 16:20
E16 = DAYS(E15;E14)

But I get a value error ...

This should work (if your date locale is set appropriately) if these are genuine date values, i.e. numbers formatted as "DD/MM/YYYY HH:MM" - but not if the cell contents are text values.

so I split data:

F14 = 23/04/2015
F15 = 25/04/2015
F16 = DAYS(F15;F14) ---> Ok, I get 2, multiply x 24 = 48h and it's fine

This mean that you *do* now have these values as genuine dates, not text.

then for the hours

G14 = 15:30
G15 = 16:20
G16 = (G15<G14)+G15-G16 (as suggested by the wiki:
https://help.libreoffice.org/Calc/Calculating_Time_Differences)
but again I have a value error..

If you really have put a formula including G16 into G16, then you have a circular reference, and you may see Error 522.

does anyone knows how can I solve this??

  If you use
=(G15<G14)+G15-G14
it should work (whether you have times or text).

It's important to understand the effect of cell formatting in spreadsheets. When you type something into a cell, the value may be converted and the cell formatting may be changed. Such changes may not be immediately evident, but you need to appreciate them if you are to see why formulae work or don't work. There are two facilities that may help:

o Unless you have specifically set cell alignment, text values will be left-aligned and numbers (including date/times) will be right-aligned.

o Go to View | Value Highlighting (or Ctrl+F8). Text values show in black and numbers (including date/times) in blue.

I trust this helps.

Brian Barker

#3

E14 = 23/04/2015 15:30
E15 = 25/04/2015 16:20
E16 = DAYS(E15;E14)

[...]

does anyone knows how can I solve this??

did you try
E16: =E15-E14
?
(IIRC set cell format to [hh] to display difference in hours)

Regards,
Nino

#4

First of all , check if both cells E14 and E15 have numbers.
menu:View>HighlightValues(Ctrl+F8).
If both values appear in blue font:
=E15-E14 gives the difference in days.

#5

Did the work below, but then came up with a simpler option.

Converts difference to minutes
=(E15-E14)/TIME(0,1,0)

Above Answer ANSWER/60
=((E15-E14)/TIME(0,1,0))/60

Above Answer INT(ANSWER/60) gives full hours
=INT(((E15-E14)/TIME(0,1,0))/60)

Above Answer MOD(ANSWR,60) gives minutes left
=MOD(((E15-E14)/TIME(0,1,0)),60)

Original way of doing it.

E14 = 04/23/15 03:30 PM
E15 = 04/25/15 04:20 PM
E16 = 48
E17 = 50
E18 = 0
E19 = 50
E20 = 48
E21 = 48.833333

Formula in E16 to calc Hours in full days
=int(e15-e14)*24

Calculates the number of minuts left
E17 = =MOD((E15-E14),1)/TIME(0,1,0)

Calculate number of Hours in partial day
E18 = =INT(E17/60)

Calc Remain Minutes
E19 = =MOD(E17,60)

Calc Total Hours
E20 = =E16+E18

Calc Hours with fraction.
E21 = =E20+E19/60

Added 12 hours to test results.

                                                   04/23/15 03:30 PM

                                                   04/26/15 04:20 AM

                                                                  48
Full Hours

                                                                 770
Extra
Minutes

                                                                  12
Hours

                                                                  50
Minutes

                                                                  60
Total
Hours

                                                       60.8333333333

Date sent: Fri, 24 Apr 2015 01:46:12 -0700 (MST)

#6

Hello and thanks you all for the kind suggestions that helped me a lot..

I have found some points:

-first of all, I don't now why but I had to use "." instead of ":" as
separator for time.. 15:30 won't be accepted while 15.30 is ok... this even
if cells were formatted as time maybe this is due to my regional settings

-for the circular reference mentioned by Brian Barker it was a mistake
copying the formula from the sheet to the webpage.. sorry for this.. Anyway,
changing the separator works fine

-when I have:
E14: 23/04/15 15.30
E15: 25/04/15 16.20

both E16: =E15-E14 and E16 = DAYS(E15;E14) give the integer number 2.0 but
using the first formula and changing format to hours and back to numbers I
have 2.034 that should be correct..

thanks again!

#7

Hello and thanks you all for the kind suggestions that helped me a lot..

I have found some points:

-first of all, I don't now why but I had to use "." instead of ":" as
separator for time.. 15:30 won't be accepted while 15.30 is ok... this even
if cells were formatted as time maybe this is due to my regional settings

-for the circular reference mentioned by Brian Barker it was a mistake
copying the formula from the sheet to the webpage.. sorry for this.. Anyway,
changing the separator works fine

-when I have:
E14: 23/04/15 15.30
E15: 25/04/15 16.20

both E16: =E15-E14 and E16 = DAYS(E15;E14) give the integer number 2.0 but
using the first formula and changing format to hours and back to numbers I
have 2.034 that should be correct..

thanks again!

#8

hello,
wonderful, the solution Michael D. Setzer II gave is amazing! Many thanks!

#9

Not sure if you want to just display the value as number as a message
B12 contains the later date - earlier date

=INT(B12/(60))&" Hours "&INT(MOD(B12+0.0001,60))&" Minutes"

Creates This
48 Hours 50 Minutes

Had to add the 0.0001 otherwise was showing 49 minutes when int used.

Date sent: Fri, 24 Apr 2015 03:49:06 -0700 (MST)

#10

Hello and thanks you all for the kind suggestions that helped me a lot..

I have found some points:

-first of all, I don't now why but I had to use "." instead of ":" as
separator for time.. 15:30 won't be accepted while 15.30 is ok... this even
if cells were formatted as time maybe this is due to my regional settings

You are working with an Italian locale.

Languages = Italian(Italy)

-for the circular reference mentioned by Brian Barker it was a mistake
copying the formula from the sheet to the webpage.. sorry for this.. Anyway,
changing the separator works fine

-when I have:
E14: 23/04/15 15.30
E15: 25/04/15 16.20

both E16: =E15-E14 and E16 = DAYS(E15;E14) give the integer number 2.0 but
using the first formula and changing format to hours and back to numbers I
have 2.034 that should be correct..

thanks again!

The cell value remains 2 (days) displayed as 48:00 (hours). If you want
to calculate with hours, multiply the day value by 24.

#11

Why? 4.3.6 is latest 4.3.x series...

It never amazes me the number of people who chase bugs using a dated
release - especially when it is the very first release in a major new
series...