1
  1. Enter the value 18/04/2020 08:10:00 in a sell.
  2. Set the formatting of the sell to Hour(1):Minute(01)
    • Actual value 18/04/2020 08:10:00
    • Display value: 8:10
  3. 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)

1 Answer 1

1

You said:

  1. Formatting for Column A is set to: Hour(1):Minute(01)
  2. 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

  1. 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 be 1054398:09:00.000 , while it will be shown as 1054398:09. Do NOT manually format the cell.

  2. 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"  |
+---------------------+-------------------------------------+
5
  • 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
  • Refresh your page. Apr 16, 2020 at 13:52
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.