Search code examples
exceltime-format

Time format not persisting

I have a calculation sheet, that checks if the rawdata has nothing in it, and if not, it will print the value of the cell.

Somehow, the timeformat is now left centered even though the cell-format and everything is the same as the above data.

I have tinkered with it for hours now and found no solution, so here i am hoping that someone knows what's going on.

SAMPLE FILE: https://ufile.io/dpe9u

Raw Data: Raw data Calculated Data: Calculated data Result: Presented result

In the calculated data the (local, "HVIS" means "IF")formula is: =HVIS('80 Rådata'!H951="";"";'80 Rådata'!H951)

I checked formatting and other settings, and everything seems the same, i even formatcopied from the rows where "it worked".

Solution

  • The data in column H sheet "80 Rådata" contains a few time values, but mainly text values that look like time values. Similar in column I, and J and K are all text values that look like times.

    You can see that when you select the values and change the cell format to "General". Those that still look like times are text.

    To fix that, select one of the columns, then click Data > Text to columns > Next > Next. In this step 3 of the wizard, specify that the data is a date (yes, a date). Then click Finish. Now all values are real time values, which you can verify by formatting as General again (then format back to time).

    Repeat that with each column in turn.

    From what I can see, the text values all have two zeros in the hour position. 00:01:30. If they had only one zero like this 0:01:30 then Excel would recognize them as time. If you don't want to repeat the data cleanup you may want to change your original data source to produce the correct format.