Query/confusion

[dantheram]

I have an excel sheet containing data exported from access. a column contains a duration in hours and is formatted [h]:mm:ss, where this time exceeds 1 day or 24 hours excel is displaying a date time in the formula bar, which is then carrying over to my pivot table.

All cells are formatted [h]:mm:ss and display the correct data, the problem is only apparent in the formula bar and pivot.

 

[Richard Schollar]

Can you change the format to a decimal number (I know this won't display properly as you want) and does that change the underlying value in the formula bar?

 

[dantheram]

Can you change the format to a decimal number (I know this won't display properly as you want) and does that change the underlying value in the formula bar?

changes 321:45:56 to 13.41 in both locations, instead of 13/01/1900 09:45:56 in the formula bar.

 

[Richard Schollar]

Can't you format it in your pivot table too (as you require)?

 

[dantheram]

I have just realised that i can do this to work round it. However, is there anyway to correct it in the raw data?

 

[Richard Schollar]

It's not really an error (it's just formatting) so I don't think you will be able to change it I'm afraid. You should be able to set options in the pivot table to preserve formatting which should preserve the format each time you refresh the PT. Beyond that I think you will have to live with what you see in the formula bar.