Format for entering durations in years and months [closed]

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

quazgar
48 ●3 ●5 ●8

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

flag of United Kingdom of Great Britain and Northern Ireland

20859 ●19 ●60 ●59 http://etmg.altervista...

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

Comments

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.

Lupp ( 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 ( 2017-10-05 19:27:43 +0100 )edit

add a comment

0

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

quazgar
48 ●3 ●5 ●8

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

Comments

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

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

add a comment

0

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

Schiavinatto

779 ●76 ●113 ●169 https://www.schiavinat...

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

Comments

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

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

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

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

add a comment

0

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

Jim K

4261 ●7 ●27 ●55

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-03in C1, which may or may not be what you want. See https://superuser.com/a/774808/541756 for details.

edit flag offensive delete link

Comments

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 ( 2017-10-05 19:19:38 +0100 )edit

add a comment

Format for entering durations in years and months [closed]

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

Comments

3 Answers

Comments

Comments

Comments

Question Tools

Stats

Related questions

Format for entering durations in years and months [closed] edit

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

Comments

3 Answers

Comments

Comments

Comments

Question Tools

Stats

Related questions

Format for entering durations in years and months [closed]

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