Ask Your Question

TIME subtr. using MOD ... ?

asked 2016-09-07 13:44:41 +0200

abi gravatar image

Why use =MOD ? Examp: a1(10:15) b1(22:03), =(MOD(b1-a1), 1)

= 3:50, WHY?

Thanks, Boswell

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-09-07 17:56:37 +0200

Lupp gravatar image
  1. Did someone here - or where else? - tell you to use MOD? If so, what was the question?
  2. =(MOD(b1-a1), 1) is simply not a valid formula and will produce an Err:511 while it is changed to =(MOD(B1-A1)~1) or something similar depending on the locale probably.
  3. Whether using the simple =B1-A1 or the strange =MOD(B1-A1;1) unnecessarily suppressing whole days, the result should be 11:48:00. No rationale for a different result.
  4. Are A1 and B1 actually conatining the values as entered data or did you post something displayed for a calculated result by some specific cell style?
  5. Why do you again invent a misleading notation like a1(10:15) where everybody else would write "A1 is containing the time value 10:15" or similar?
  6. Helpers are well capable of reading and understanding a question of 5 lines in less than a minute. The wasting of time starts with the unnecessary guessing concerning letters and digits arranged as if dropped from the moon.
edit flag offensive delete link more

answered 2016-09-07 18:55:36 +0200

mark_t gravatar image

updated 2016-09-07 19:05:21 +0200

I think using MOD function in this way is a workaround from Excel where negative values for time format will be displayed as ######################.

With LibreOffice negative values for time are treated as the time in the previous day, Example -0.2 and 0.8 if shown in time format would both have the same displayed value 19:22:00. Although they may have different result in any further calculations.

If you may have calculations that return negative values for time and you want to maintain compatibility with Excel then you could consider using MOD(x,1) where x is a calculated time difference. This gives the same displayed value in both Excel and LibreOffice.

You need to be very carefull treating negative results for time values in this way.

Example A1 = 10:15, B1 = 22:03

MOD(B1-A1,1) result is 11:48:00

MOD(A1-B1,1) result is 12:12:00 which makes no sense.

It might be better to use the IF function to make sure that negative time differences are not treated this way.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-07 13:44:41 +0200

Seen: 141 times

Last updated: Sep 07 '16