Format for entering durations in years and months

English
,
#1

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.

#2

Needinfo
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.

#3

> 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.

#4

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

#5

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

#6

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

#7

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.

=DATE(YEAR(A1)+LEFT(B1,2), MONTH(A1)+RIGHT(B1,2), DAY(A1))

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

#8

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.

#9

Combining the answers by @Gilberto Schiavinatto and @jimk, 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.

#10

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

#11

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.