Date manipulations using libre office calc

[GrahamPlatt]

Hi, wonder if anyone can help here. I’m trying to merge two columns for sorting. One has dates as e.g 20181231 and the other 31/12/2018. I’m on v. 6.4.6.2 and find on t’web that there should be a function CDateFromIso() which can convert former to latter, but all I get is #NAME? The function is not listed in the date & time functions in the program itself.

[modellingman]

I'm not an expert on Libre Office, but a quick Google on the function name suggests that it is not a worksheet function but a function available in the basic programming language used for macros.

In Excel, my approach would be to convert yyyymmdd into a string (text), use the LEFT(), MID() and RIGHT() worksheet functions to get the year, month and day parts. Convert these back to numbers, and then use the DATE() function to create the date. Other approaches are, no doubt, available.

[johnhemming]

I use open office from time to time and have had a copy of Libre office as well, but I don't know the detailed answer. There probably is a solution with string processing, however.

[modellingman]

I'm not an expert on Libre Office, but a quick Google on the function name suggests that it is not a worksheet function but a function available in the basic programming language used for macros.

In Excel, my approach would be to convert yyyymmdd into a string (text), use the LEFT(), MID() and RIGHT() worksheet functions to get the year, month and day parts. Convert these back to numbers, and then use the DATE() function to create the date. Other approaches are, no doubt, available.

To add flesh to the bones...

Assuming the yyyymmdd is a number held in cell A3 the Excel formula would be

=DATE(VALUE(LEFT(TEXT(A3,"########"),4)),VALUE(MID(TEXT(A3,"########"),5,2)),VALUE(RIGHT(TEXT(A3,"########"),2)))

Alternatively, using Excel's new LET() function (available only in the Office 365 version I suspect) the repetitious TEXT(A3,"########") can be avoided using

=LET(STRING,TEXT(A3,"########"),DATE(VALUE(LEFT(STRING,4)),VALUE(MID(STRING,5,2)),VALUE(RIGHT(STRING,2))))

IIRC the worksheet function names in Libre Office tend to be pretty much the same as Excel's in which case the first of the two formulae above should work.

[Stompa]

Alternatively, you could do it numerically with:

=DATE(QUOTIENT(A1,10000),MOD(QUOTIENT(A1,100),100),MOD(A1,100))

[GrahamPlatt]

Thanks Stompa & modellingman, indeed, thanks everyone.
I had a little trouble simply reproducing those formulae, but they both work.
Cheers

[Breelander]

...Assuming the yyyymmdd is a number held in cell A3 the Excel formula would be

=DATE(VALUE(LEFT(TEXT(A3,"########"),4)),VALUE(MID(TEXT(A3,"########"),5,2)),VALUE(RIGHT(TEXT(A3,"########"),2)))

That works equally well in LibreOffice (I've just tested it).

Alternatively, using Excel's new LET() function (available only in the Office 365 version I suspect)....

Actually, not even there (yet)...

Note: This is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Microsoft 365 subscribers.

https://support.microsoft.com/en-us/off ... b8f9908999

[modellingman]

Alternatively, using Excel's new LET() function (available only in the Office 365 version I suspect)....

Actually, not even there (yet)...

Note: This is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Microsoft 365 subscribers.

https://support.microsoft.com/en-us/off ... b8f9908999

Not sure why my Office 365 version provides me with access to the LET function, I'm pretty sure I haven't signed up to any early release programme.

I'm not a huge fan of hideously long formulae and generally prefer to split calculations across several cells. So, whilst I suspect I won't make huge use of it in future, I was aware of its existence and thought I'd give it a whirl. It worked.

[dragnips]

Here's what I think maybe an easier way:
Cursor in data cell (20181231)
Select Data/Text to Columns
Select 'Standard' and then click on the 'down' arrow at RHS. Select 'Date (YMD)' and the data becomes '31/12/18'.
If you specifically need 2018 instead of 18, you can convert this by using Format/Cells/Date and selecting the DD/MM/YYYY format.