Calc question on dates in formulas

users
#1

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.

#2

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

#3

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

#4

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

#5

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.