The solution depends on how the hyperlinks are created. There are two ways to generate a hyperlink in Calc:
- Menu
Insert
-> Hyperlink
- using the
HYPERLINK()
function.
In the first case, LO Calc inserts a field.
In the second case, the result is "plain text" with additional functionality if the user clicks on the hyperlink text.
Formatting a "text hyperlink"
Formatting a "text hyperlink" (created using the HYPERLINK()
function) works the same way as formatting any other text content: using direct formatting - applying text style properties directly using the toolbar or Menu Format
-> Text
; or using cell styles.
Formatting a "field hyperlink"
Formatting a "field hyperlink" (created using Menu Insert -> Hyperlink) is somewhat more complicated, but it has the advantage that LO will apply a certain styling by default:
The actual design of "field hyperlinks" in Calc is the result of two settings: Field shading, and link color. You can't set the link color on a per-document basis, instead changes are applied application-wide (will affect hyperlinks in writer, too).
LibreOffice <= 5.0.x.x:
To modify the "field hyperlink" link color (and in calc, the link background -> field shading):
Menu Tools
-> Options
-> LibreOffice
-> Appearance
. Search for "unvisited links
" or "visited links
", and change the color:
For the field background in Calc, scroll down to the Text Document
section (!), and change the "field shadings
" setting.
LibreOffice >= 5.1.x.x:
To modify the link color (and in calc, the link background -> field shading):
Menu Tools
-> Options
-> LibreOffice
-> Application Colors
. Search for "Unvisited links
" or "Visited links
", and change the color:
For the field background in Calc, scroll down to the Text Document
section (!), and change the "Field Shadings
" setting.
Important:
Take care to tick the checkboxes at the left of the setting name (in the second screenshot: marked with red rectangle).
Important, too:
Don't apply magenta to hyperlinks, as in the second screenshoot - looks extremely ugly...
(LO 5.1.x: Tested with both Linux x86_64 and Windows 10)
Best Answer
If
H2
contains the number to transform (1517335200000
).Make
H3
contain the formula:= H2/1000/(60*60*24) + 25569
Which will return the number 43130.75.
Change format of cell
H3
to date. Either:Change format of the
H3
cell to the required date format:Expand width of cell if not wide enough to show the desired format (hint: three # appear).
Why:
Epoch time is in seconds since 1/1/1970.
Calc internal time is in days since 12/30/1899.
So, to get a correct result in H3:
Get the correct number (last formula):
But the epoch value you are giving is too big, it is three zeros bigger than it should. Should be 1517335200 instead of 1517335200000. It seems to be given in milliseconds. So, divide by 1000. With that change, the formula gives:
Change the format of
H3
to date and time (Format --> Cells --> Numbers --> Date --> Date and time) and you will see:in H3.
Of course, since Unix epoch time is always based on UTC (+0 meridian), the result above needs to be shifted as many hours as the local Time zone is distant from UTC. So, to get the local time, if the Time zone is Pacific standard time GMT-8, we need to add (-8) hours. The formula for H3 with the local time zone (-8) in H4 would be:
And presented as:
if the format of H3 is set to such time format.