[Solved] Insert timestamp date in a string

[cwr02]

The timestamp for 03:06 PM on June 15, 2025, is 1750000000. If I format a cell to display this date as MM/DD/YY HH:MM AM/PM, and enter the following into the cell:

Code: Select all

=DATE(1970,1,1)+1750000000/60/60/24

This produces the following desired cell contents:

Code: Select all

06/15/25 03:06 PM

Now imagine that I want to insert this date in a string. The command to do this is...

Code: Select all

=TEXT(DATE(1970,1,1)+1750000000/60/60/24, "mm/dd/yy hh:ss am/pm")

BUT the resulting cell contents is not what you would expect. It is...

Code: Select all

06/15/25 03:40 PM

Is this a bug in Calc?

[MrProgrammer]

=TEXT(DATE(1970,1,1)+1750000000/60/60/24, "mm/dd/yy hh:ss am/pm")

The formula result is correct. Try using hh:mm instead of hh:ss in your formula.
Personally I would use =TEXT(1750000000*"00:00:01"+"1970-01-01";"YYYY-MM-DD HH:MM:SS").

OpenOffice version: 25.2.5.2

The latest OpenOffice version is 4.1.15 so 25.2.5.2 does not exist. I will assume you use LibreOffice. Fix your forum signature in User Control Panel → Profile → Edit Signature.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[cwr02]

Many thanks, MrProgrammer. Silly of me not to have noticed ss instead of mm!