Ask Your Question

[calc] What's wrong w/S.S formula?

asked 2019-11-13 13:22:16 +0100

abi gravatar image

HP Pavilion 9...; win 10;

C:\fakepath\SLEEP_Tm.ods Please focus your attention on the last two, [30, 31] Rows. I purposely did Not enter new data … SO WHY SUCH A LARGE DIFFEDRENCE ?

Thank you!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-11-13 13:40:40 +0100

updated 2019-11-13 13:55:35 +0100

When working with durations, never (yes, never!) use wall time format like HH:MM; always use duration formats like [HH]:MM, which will easily show your problems.

And when working with times, be aware that drag-copy operations on numbers (and times) typically increase numbers by one: test with a cell with value 1, and drag-copy it down (select the cell and use mouse to drag the small dot in the bottom right part): it will turn 2. When a time cell (with something like "19:45", which is actually a number like 0.822916666666667 formatted like a time) is dragged down, it is also incremented by 1. But having formatting in the cell like "HH:MM", which is possibly something you actually need here (if you register some wall time here), you won't see that added 1 (which would be 1 whole day), and only see "19:45" again, without realizing that the value is now much greater.

edit flag offensive delete link more


Mike; I certainly thank you for making me aware of these issues. It's clear, I need to do some serious learning - especially on Time FORMATS.
Can you recommend a worthy text ?

abi gravatar imageabi ( 2019-11-14 00:06:37 +0100 )edit

The full format code syntax is described in our help.

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-14 06:27:33 +0100 )edit

Mike; I did change the frmts > [HH: SS] - NO observable difference. Drag'ng down. and adding a day, as you explained, does skew the value. Is there a way to cancel that? Or how should a new line be formed?

I value your help greatly, Thanks!

abi gravatar imageabi ( 2019-11-15 14:49:11 +0100 )edit

Have I ever mentioned [HH: SS], i.e. square brackets around the whole format??? Have you paid attention to what was written/suggested to you - i.e. [HH]:MM with only most significant part bracketed?

To not add the 1 to the number while drag-copying, you need to press Ctrl while releasing mouse button.

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-17 08:49:57 +0100 )edit

answered 2019-11-13 13:47:10 +0100

Opaque gravatar image

updated 2019-11-13 13:47:40 +0100


probaby because in fact you did not add day times in cells B30 and B31 they contain

B30: 43:45:00 and B31: 67:45:00

(same thing for cell C31, which is 47:55:00, D31 (26:21:00), E31 (27:40:00)

which aren't times of a day, but time differences (Time of a day must be less or equal 1)

Hope that helps

edit flag offensive delete link more


The reason is that OP simply drag-copies times down, without noticing that this operation adds 24 hours to each cell with date/time.

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-13 13:48:50 +0100 )edit

The rest I'd like to add has already be done by @Mike Kaganski 's answer, which crossed with my first version of the my answer. Sorry @Mike Kaganski...

Opaque gravatar imageOpaque ( 2019-11-13 13:49:25 +0100 )edit

@Mike Kaganski

without noticing that this operation adds 24 hour

Joking / Nitpicking: It adds 1 ;-)

Opaque gravatar imageOpaque ( 2019-11-13 13:51:31 +0100 )edit

Please don't be sorry: crossing answers are not something to ask excuses for - we all try to help others! Thank you (and me ;-P) for that!

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-13 14:02:55 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-13 13:22:16 +0100

Seen: 64 times

Last updated: Nov 13 '19