Problem extracting text (concerning dates)

English
#1

Hello. I am using LibreOffice version 7.5 on a Windows 10 desktop.
.
I have a Calc spreadsheet that contains the following two columns:

  1. Date field (ie., 1992-05-18) - column F
  2. Year field (ie., 1992) - formatted with user-defined YYYY that contains a function =TEXT(F1,“yyyy”) to extract the year from the Date field.
    .
    This function works fine IF the Date field contains a complete date. But unfortunately, there are some instances wherein I do not know the month or day, so I use an “x” character to represent the unknown.
    For example: 1992-05-xx or 1992-xx-xx.
    .
    Is there any way to extract only the year (first 4 digits) if I have to use “xx” in the Date field?
    .
    In addition, in some cases I do not even know the year, so I will type “1990s” in the Date field. In those cases, I need the extracted text in the Year field to be “1990s”.
    .
    Any suggestions will be much appreciated! :slightly_smiling_face:
#2

You could use instead =LEFT(F1;4). The problem is that it will be text, not year. As some of the dates are text anyway this might not be an issue.

#4

Thank you for your suggestion, @EarnestAl . Unfortunately, the Date field is not formatted as text (even though I have had to include some exceptions). As a result, the “LEFT” function will not work by itself.

#3

disask92648enhancedGetYear.ods (24.2 KB)

1 Like
#5

Thank you for your sample spreadsheet, @Lupp . This formula does solve the problem of being able to extract the year regardless of the Date field’s format. I do appreciate it! :smiley:
.
However, I’m still wondering if there is some way to also handle my third scenario wherein I don’t know the exact year and have typed the decade with an “s” on the end (for example, 1980s or 1990s). Is there any way to modify your formula to handle all 3 scenarios:
If it is a date, then YEAR(cell), else
If it is not a date, then LEFT (cell,4), else
If it contains an “s”, then LEFT(cell,4) + “s”
.
I attempted the following, but it did not add the “s” on the end:
=IF(ISNUMBER(F1),YEAR(F1),IFERROR(VALUE(LEFT(F1,4)),CONCAT(LEFT(F1,4),"s")))
.
THANKS again!

#6

In my opinion it is better to uniform all of your dates to formatted numeric value ot to pure text Type.

#7

Of course, this is possible, but next time you may have a case where not just a “standard decade” (YYYY starting with 0 at the end) shall be designated, but a “running decade” starting with a different year. Another time you may want to be a bit more precise, and to add a month. Moreover there may come up cases where you want to define the startdate and the enddate independently - or only a startdate, but accompanied by a duration.
There is an international institution that has employed smart people to think through all the relevant cases, and specify standard formats for them. They were officially mandated to do so. This is ISO, and the applicable standard is ISO 8601 as described here in the wikipedia. We should try to not invent a different “personal standard” for every spreadsheet.
In your case (for the given example) there would be more than one conformant representation. the most appropriate one should be 1980-01-01/P10Y. In addition I wouldn’t object agains an abbreviation for something like “year only” not explicitly specified by the mentioned standard, but clear in itself: 1980--/P10Y omitting month and day of the start date as either “explained as mandatory elsewhere” or “irrelevant”.
Please consider the suggestion.
See also:
disask92648enhancedGetYearUp1.ods (24.3 KB)

#8

Thank you for your suggestions, @Lupp . I understand the concerns, but this spreadsheet catalogs a large collection of old photographs… so, in my instances, I simply do NOT know the exact year the picture was taken, but I DO know the decade (1970s, 1980s, 1990s, etc.).
.
I store the photographs in folders with only two naming conventions: 1. either by the YEAR (ie., 1974), or
2. by the DECADE (ie., 1970s). I add an “s” to the end of the decade to differentiate it from the exact year of 1970.
.
The calculation determines the FOLDER name… which will later be concatenated with the filename to create a pathway hyperlink to display the picture from the spreadsheet. (This worked well in Excel, so I hope I can make it work in Calc.)
.
THANK YOU so much for the spreadsheet example that demonstrates the “FIND” function… I think it will work very well for my purposes.

#9

Thanks for the thanks.

You can do as you like, of course.
However there are some facts not to your disposition.
…

When I started learning English almost 70 years ago, there was a photo in my English book of an American warning sign about the danger of forest fires. Underneath the picture of burning trees was written:
One tree can make a million matches.
One match can destroy a million trees.

Now I feel tempted to draft a sign about wasting time posting to Q&A sites:
One spreadsheet can easily violate a hundred standards.
One standard followed could save a few thousand questions.

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