LibreCalc Format Cells

English
#1

I’ve just installed LibreOffice to replace MS Office because I lost my old license and refuse to pay their prices. So far, I’ve only tried to use LibreCalc. I imported a spreadsheet file previously created and updated it. I discovered that data I pasted into the spreadsheet contains a date column that also includes a time stamp. I was able to format the column to include just the date in format mm/dd/yyyy in Excel and it would drop the time stamp. So far, after trying various ways to format the column, I’ve been unsuccessful.

I could post a screenshot here if I knew how.

Thanks for any help.

#2

you have this icon to attach screenshots and spreadsheets : upl

#3

I don’t think excel will really drop the time - but let’s assume you are the expert for excel. In Calc formatting don’t change a value, it only changes what you see instead.
.
A date with time is a floating point number, where the integer part is the number of days usually since beginning of 1900. Time is coded as fraction of 24 hours (6 AM = 0.25 / 12 = 0.5) so all you need is to round down the value.
https://wiki.documentfoundation.org/Documentation/Calc_Functions/TRUNC

#4

If the spreadsheet worked in Excel as xls or xlsx then it should display the same formatting in Calc. Formatting works the same in both. Probably, the dates are text, not dates, you can check by clicking View > Value Highlighting, dates should be coloured blue while text is coloured black. Excel makes assumptions about your data which is helpful when it is correct but upsetting when it isn’t; Calc respects your data even if it looks like something else.

See this wiki page to convert text, Calc Number text to Numeric data

#5

Thanks, fpy. I shall use that in future.

#6

I have no idea what the data is actually encoded as. It is data downloaded from a web site that reports electricity meter readings. I track this data daily and have for several years. The spreadsheet that I copy the data into has cells that match to the cells from the web site. There are ‘from’ and ‘to’ dates defining the period that the meter readings come from. The ‘from’ value is a date appearing in mm/dd/yyyy format. The data as downloaded for the ‘to’ value appears in mm/dd/yyyy hh:mm format if my column is not formatted. I have the column formatted as a date value and use the pattern mm/dd/yyyy. In Excel, the formatting caused the data to appear with only the mm/dd/yyyy values without the hh:mm values; however, in LibreCalc, choosing Format Cells and selecting the Date format with pattern mm/dd/yyyy does not have the same effect: the hh:mm values continue to appear.

I hope I’m making this clear. I probably misspoke when I said that Excel would drop the time stamp (the hh:mm) portion of the data. Probably the formatting pattern applied to cell simply did not display that part.

#7

Thanks for the information. I tried to make a better explanation in a prior response in this thread.
In the first image, you can see how the data appears in a spreadsheet page created in Excel. In the second image, you can see how the data appears in the active spreadsheet page. This is after formatting the TO DATE column and after clicking View > Value Highlighting. From your description, the entire value in the TO DATE column must be text as it all appears in black.

Does this help explain what I’m trying to accomplish and show the results I’m getting?

Partial Electricity Spreadsheet - excel
Partial Electricity Spreadsheet - excel865×598 144 KB

Well, that didn’t work. New users can’t include but 1 upload in a post. Standby, I’ll end this one and make another.

#8

OK, here’s the screenshot showing results I’m getting in LibreCalc.

Partial Electricity Spreadsheet - librecalcl
Partial Electricity Spreadsheet - librecalcl892×484 117 KB

#9

Format column “To Date” same as “From Date”. Either select the column and click Format > Cells (Crtl+1) and in the Number tab enter MM/DD/YYYY as the format or use the Clone Formatting paintbrush icon, just like Excel

#10

You should find out…
.
There are two typical ways: Either you copy and paste directly from your browser, or saving data as csv.
.
Usually Calc shows you a preview of the data you import and you should realize you can actually change the type of the columns there. Why is this necessary? No software can decide if 04/05/06 is 6th of May in 2004 or 4th of May in 2006, without additional information from you. So Calc imports this as text, if you don’t help. But Text will not obey to “formatting” as Date.

1 Like
#11

I’ve tried that. The TO DATE column continues to have the hours and minutes displayed.

#12

Thanks, I’ll give that a try.

Meanwhile, the problem persists: The column contains date and time when only date should be displayed according to the format date pattern chosen, ie, mm/dd/yyyy.

#13

Did you do anything to change the situation?
.
If you like, create a copy of your spreadsheet and upload a reduced version here. So one can check, if your columns contain text or something unexpected…
.
The FAQ on the topic was already linked by @EarnestAl

#14

Screenshots are not very helpful compared to a sample file. When I look at the screenshot, it really looks as if the timestamp is text only. If you have set value highlighting then it is black, indicating text, it is also left aligned.

Format the column for the exact appearance of the date time stamp. After pressing Enter you should see an apostrophe at the beginning of the time stamp. Select the column and do the Find and Replace proposed in the linked FAQ

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