Calc question on dates in formulas

classic Classic list List threaded Threaded
5 messages Options
Budge Budge
Reply | Threaded
Open this post in threaded view
|

Calc question on dates in formulas

Getting into a muddle here so if anybody could straighten me out it
would be appreciated.

I have several columns in sheet with dates in the format 25 Jun 2014
which have been imported into Calc via a .csv file from another programme.

I have to manipulate the data and then export it as a .csv file for
import by yet another program which requires the dates in the format
25/06/2014.

I have been able to create a column with the dates in required format
using the formula =TEXT(B2,"DD/MM/YYYY") but this leaves the underlying
formula in the cell so when I try and remove unwanted columns the
formula fails.  What I need is the date as written by the formula
"frozen" as text so I may then manipulate the columns without messing
up.  Hope this is clear.

Please could somebody advise how I should proceed.

--
To unsubscribe e-mail to: [hidden email]
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

OogieM OogieM
Reply | Threaded
Open this post in threaded view
|

Re: Calc question on dates in formulas


On Jun 25, 2014, at 9:52 AM, Budgie <[hidden email]> wrote:

> I have been able to create a column with the dates in required format using the formula =TEXT(B2,"DD/MM/YYYY") but this leaves the underlying formula in the cell so when I try and remove unwanted columns the formula fails.  What I need is the date as written by the formula "frozen" as text so I may then manipulate the columns without messing up.  Hope this is clear.

Once you create your column with the date formatted properly copy it and paste special into a new column including only the text or numbers.


Eugenie (Oogie) McGuire
Desert Weyr, LLC - Black Welsh Mountain Sheep http://www.desertweyr.com/ 
LambTracker - Open Source SW for Shepherds http://www.lambtracker.com
Paonia, CO USA


--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Calc question on dates in formulas

In reply to this post by Budge
At 16:52 25/06/2014 +0100, Budgie Noname wrote:

>I have several columns in sheet with dates in the format 25 Jun 2014
>which have been imported into Calc via a .csv file from another
>programme. I have to manipulate the data and then export it as a
>.csv file for import by yet another program which requires the dates
>in the format 25/06/2014. I have been able to create a column with
>the dates in required format using the formula
>=TEXT(B2,"DD/MM/YYYY") but this leaves the underlying formula in the
>cell so when I try and remove unwanted columns the formula
>fails.  What I need is the date as written by the formula "frozen"
>as text so I may then manipulate the columns without messing up.

As so often, Paste Special... is your friend. Copy the results of
your formula and paste them back - either over themselves or even
over the original data - but using Paste Special... (or Ctrl+Shift+V)
instead of ordinary Paste. In the Paste Special dialogue, ensure that
Paste All is not ticked and then that Formulae is also not ticked.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

upscope upscope
Reply | Threaded
Open this post in threaded view
|

Re: Calc question on dates in formulas

In reply to this post by Budge
On Wednesday, June 25, 2014 04:52:24 PM Budgie wrote:
> 25 Jun 2014
Not sure about openoffice but in LibreOffice you can start calc,  go to
 Format -->Cells --> Date and create a format like you want by inserting
in the Format Code box what you want:

Example:  DD/MM/YYYY this will give 25/06/2014.

Then go  to the spreadsheet column, highlight it or just the cells you
want to have it. When you insert a date in the formatted empty column it
will appear as the format you want.

Russ


--
openSUSE 13.1(Linux 3.11.10-17-desktop x86_64|
Intel(R) Quad Core(TM) i5-4440 CPU @ 3.10GHz|8GB DDR3|
GeForce 8400GS (NVIDIA-Linux-x86_64-331.79)|KDE 4.13.2

--
To unsubscribe e-mail to: [hidden email]
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

Russ
Budge Budge
Reply | Threaded
Open this post in threaded view
|

Re: Calc question on dates in formulas

In reply to this post by OogieM
On 25/06/14 18:13, Oogie McGuire wrote:

>
> On Jun 25, 2014, at 9:52 AM, Budgie <[hidden email]> wrote:
>
>> I have been able to create a column with the dates in required format using the formula =TEXT(B2,"DD/MM/YYYY") but this leaves the underlying formula in the cell so when I try and remove unwanted columns the formula fails.  What I need is the date as written by the formula "frozen" as text so I may then manipulate the columns without messing up.  Hope this is clear.
>
> Once you create your column with the date formatted properly copy it and paste special into a new column including only the text or numbers.
>
>
> Eugenie (Oogie) McGuire
> Desert Weyr, LLC - Black Welsh Mountain Sheep http://www.desertweyr.com/
> LambTracker - Open Source SW for Shepherds http://www.lambtracker.com
> Paonia, CO USA
>
>
Many thanks Eugenie, I have it now but got there a long way round.

I was very interested in the sheep links and your farming objectives,
which we share.  We do not have rare sheep at present although we have a
few sheep and the tagging looks interesting.  We also have a few
Shetland cattle, which are rare, and have just completed small extension
to the farmhouse for a commercial kitchen so we can open restaurant.

Thanks again for your help.
Regards,
Alastair.

--
To unsubscribe e-mail to: [hidden email]
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