- Enter the value
18/04/2020 08:10:00
in a sell. - Set the formatting of the sell to
Hour(1):Minute(01)
- Actual value
18/04/2020 08:10:00
- Display value:
8:10
- Actual value
- Edit the value of the sell to become
18/04/2020 08:11:00
- Actual value
18/04/2020 08:11:00
- Expected Display value:
8:11
- Expected Formatting:
Hour(1):Minute(01)
(as set earlier) - Current Display value:
18/04/2020 08:11:00
- Current Formatting:
Day(05)/Month(08)/Year(1930) Hour(01):Minute(01):Second(01)
- Actual value
1 Answer
You said:
- Formatting for Column A is set to:
Hour(1):Minute(01)
- The values in that column are like:
1054496:09
(Timestamp with date)
I am not able to reproduce your formatting.
If one uses Hour(1):Minute(01)
to change the timestamp 16/04/2020 08:09:00
to 1054496:09
, Google sheets automatically converts the cell to 8:09
while inside the cell one reads 16/04/2020 08:09:00
or 04/16/2020 08:09:00
(depending on the locale of the sheet).
In any case...
Solution
Copy the value of any of your converted timestamps in the format
1054398:09
and paste it in an unformatted cell. The value in the cell will be1054398:09:00.000
, while it will be shown as1054398:09
. Do NOT manually format the cell.Use the
Paint format
(Roller) tool from the menu and format the other cells. Do NOT manually format the cells.
These formatted cells will stay in the format 1054398:09
no matter the changes.
+---------------------+-------------------------------------+
| 18/04/2020 08:10:00 | Formatted as Timestamp/Date time |
+---------------------+-------------------------------------+
| 8:10 | Formatted as Hour(1):Minute(01) |
+---------------------+-------------------------------------+
| 1054544:10:00 | UNFORMATTED/Formatted as "nothing" |
+---------------------+-------------------------------------+
-
I'm really so sorry. I made a mistake. the format is not 1054496:09 I copy pasted value only to some other random sell and this appeared so I thought this is the basic format for timestamp. I updated my question. Apr 16, 2020 at 13:43
-
"". I don't see it updated. Also, since the answer to your original question is correct, I would suggest accepting it and posting a new question. Otherwise users will be confused. Apr 16, 2020 at 13:51
-
-
I can see it now. Still. As mentioned before -since now your question is completely different- "I would suggest accepting it and posting a new question. Otherwise users will be confused". Apr 16, 2020 at 14:02
-
But I do not understand what you understanding of my problem was, this is not a completely different question. The only problem was that I mistakenly thought that
1054544:10:00
was the definition of a timestamp which was not the case. However I had ever written in parenthesis that I meant timestamp with date by that. I do not see this as a different question. I see this as a valid question and the other one as an invalid misguiding question. Apr 16, 2020 at 14:50