2013 Archives by date, by thread · List index

# Re: [libreoffice-users] [Calc] how to make this formula more "compact"?

```At 00:03 09/08/2013 +0200, Gordom Noname wrote:
```
I have a series of data. Here you have a small sample (this data is placed into a column A):
```,,08,01,17,27,13

```
Each cell must be formatted into a proper date & time format. I'm achieving this using the following formula in column B (the following example corresponds to cell No A1): =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A1;1;1;"2013");11;1;" ");5;1;"-");8;1;"-");14;1;":");17;1;":")
```
As the result I get this in cell B1:
2013-08-01 17:27:13

```
My question: Is there a way to simplify the formula? However it works, it doesn't seem to be very elegant right now.
```
I'd probably do something like
```
="2013-"&MID(A1;3;2)&"-"&MID(A1;6;2)&" "&MID(A1;9;2)&":"&MID(A1;12;2)&":"&RIGHT(A1;2)
```instead.  Only you can decide whether this is more elegant.

```
But notice that your formula does not create what I would call a "proper date and time format", but instead a text string that looks like a date and time value. (So does my version, of course.) To see the difference, try adding 1 to the date and time value. In your case you will get the result 1, since the text string is treated as zero, but adding one to a genuine date and time value produces a time exactly one day later. You can convert the result of either formula to a real date and time using
```=DATEVALUE(B1)+TIMEVALUE(B1)
and then formatting the result cell appropriately (as YYYY-MM-DD HH:MM:SS).

You can build this idea into my version by using
=DATEVALUE("2013-"&MID(A1;3;2)&"-"&MID(A1;6;2))+TIMEVALUE(MID(A1;9;2)&":"&MID(A1;12;2)&":"&RIGHT(A1;2))

But that leads to what is perhaps a simpler version:
=DATE(2013;MID(A1;3;2);MID(A1;6;2))+TIME(MID(A1;9;2);MID(A1;12;2);RIGHT(A1;2))
```
Again, you need to format the result cell to display the date and time as you wish to see them.
```
I trust this helps.

Brian Barker

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
```