Ask Your Question
0

Calc: sum up dates in from different columns in a third column?

asked 2015-05-21 17:23:42 +0200

Nils-EuroClix gravatar image

updated 2015-08-26 21:39:04 +0200

Alex Kemp gravatar image

Hi,

I have an issue with Calc. I'm using libre Office Version: 4.3.4.1 , english .

I have a column containing full dates (including time), to which I need to add 9 hours (in another column).

The first column is formated as DD-MM-JJJJ UU:MM:SS

Second column is formated as UU:MM

I now want to add these columns

In a third column, I am using formula =Column1+Column2 (Or "=O2+N2" , next row "O3+N3" , etc.) The formulas get adapted coorectly according to each row.

Still, 9 hours get added, but also another day gets added! The final date is not 30-04 anymore, but 1-05, 02-05 etc. even though this cannot be explained by 9 hour time difference.

How come? What can I do to prevent this (just 9 hours need to be added to the original date...)? Does anyone know?

Kind regards,

Nils

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-05-21 18:09:43 +0200

Lupp gravatar image

updated 2015-05-21 18:21:43 +0200

Are you sure that the cell showing 9:00 actually contains the numeric value 9/24 = 3/8 = 0,375?

You should be aware of the fact that "Time Of Day" and a "Duration" are very different things. Never format a duration with the code "HH:MM" or "HH:MM:SS" or similar. Always use "[HH]" or "[H]" for the first part to make sure that nothing most relavant (a full day, e.g. caused by an added value 1) is suppressed for the display.

If this was not your problem, please attach an example document demonstrating the error.

See also attached. ask50954AddDurationToDateTime001.ods

edit flag offensive delete link more

Comments

If what @Lupp comment doesn't solve the problem, I remember some issues about dates with some time zones. https://bugs.documentfoundation.org/s...

m.a.riosv gravatar imagem.a.riosv ( 2015-05-22 00:30:40 +0200 )edit

problem solved! Thank you, this was the exact answer to my problem! It was thus indeed a formatting error, and not a bug. Again, thanks a lot!

Nils-EuroClix gravatar imageNils-EuroClix ( 2015-05-27 13:27:44 +0200 )edit

@Nils-EuroClix Glad to have helped. You might mark the answer as correct then.

Lupp gravatar imageLupp ( 2015-05-27 14:53:08 +0200 )edit

Not enough points to vote, yet, sorry ;)

Nils-EuroClix gravatar imageNils-EuroClix ( 2015-06-22 17:16:54 +0200 )edit

Don't worry!

Lupp gravatar imageLupp ( 2015-06-22 17:54:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-05-21 17:23:42 +0200

Seen: 424 times

Last updated: May 21 '15