Ask Your Question

Format for entering durations in years and months [closed]

asked 2017-10-03 19:56:09 +0100

quazgar gravatar image

updated 2020-08-07 19:57:49 +0100

Alex Kemp gravatar image

Hi, I am looking for a way to enter time durations involving years and months. Ideally in the format YY-MM. But when I set the format to such a format and try to enter "6 months" as 00-06, it is interpreted as 2000-06 (displayed as 00-06 though), whereas adding this date to another date adds another 100 years, because LibreOffice's epoch seems to be 1900-01-01.

So, how can I manually enter durations and visually see if they were entered correctly?

Note: As a programmer I know that working with times is a PITA, always.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-07 19:59:31.174297


What do you expect to be the actual content of a cell,where you entered the "YY-MM" thing"? Should it be a text or a numeric value? How should the final durations be represented?
Please go into the details.

Lupp gravatar imageLupp ( 2017-10-04 18:41:39 +0100 )edit

> What do you expect to be the actual content of a cell That does not really matter that much to me, I don't care if it's a string or numeric. The outcome should not be a duration, but a date.

quazgar gravatar imagequazgar ( 2017-10-05 19:27:43 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2017-10-05 19:25:12 +0100

quazgar gravatar image

Combining the answers by @Gilberto Schiavinatto and @Jim K, formatting the delta B1 as Number 00-00 (just formatting sugar, hundreds count as years, everything below as months), the following works for me:

EDATE(A1, RIGHT(B1, 2) + INT(B1/100) * 12)

I was expecting a less manual approach though (along the lines of numpy's timedelta64, for example), but this works for me.

edit flag offensive delete link more


It's a mess. And a lot of wasted time.

Lupp gravatar imageLupp ( 2017-10-05 21:39:21 +0100 )edit

answered 2017-10-04 03:19:14 +0100

updated 2017-10-04 17:09:06 +0100

Hi @quazgar,

Format the input cell: Number 00-00 and use this formula for the sum:

= IF ((C8 - ((INT (C8 / 100)) 100)) + (C9 - ((INT (C9 / 100)) * 100)))> 11) (C8 - ((INT (C8 / 100)) 100)) + (C9 - ((INT (C9 / 100)) 100) INT (C8 / 100)) * 100 + C9 - ((INT (C9 / 100)) 100 * (C8 / 100) * 100)) + (INT (C8 / 100)) * 100+ (INT (C9 / 100)) * 100)

, where C8 and C9 are the input cells.

test file

edit flag offensive delete link more


What is SE? It produces #NAME on my system. Also, the parentheses are not matched correctly.

Jim K gravatar imageJim K ( 2017-10-04 15:37:26 +0100 )edit

se = pt-br ; if = en. Replace with if

Schiavinatto gravatar imageSchiavinatto ( 2017-10-04 17:06:03 +0100 )edit

answered 2017-10-04 15:20:32 +0100

Jim K gravatar image

These durations are not actual dates, so do not use YY or MM. Instead, format B1 as Number 00-00 as recommended by @Gilberto Schiavinatto. Then enter 2014-01-31 in A1 and 00-06 in B1. Format C1 as a date and enter this formula.


How do you want to handle short months such as February? With the formula above, entering 00-01 in B1 produces 2014-03-03in C1, which may or may not be what you want. See for details.

edit flag offensive delete link more


Thanks, that's already very useful, especially the link with the hint to edate. Unfortunately, LEFT(B1, 2) yields 6 for 00-06, because that is just a representation of the number 6. I will work on a solution though.

quazgar gravatar imagequazgar ( 2017-10-05 19:19:38 +0100 )edit

Question Tools

1 follower


Asked: 2017-10-03 19:56:09 +0100

Seen: 308 times

Last updated: Oct 05 '17