### showing up in pivot table.

[Judy]

My data has a col called Dur which is a calculation subtracting one time from
another time. Depending on my filtering in the pivot table depends on how
many cells contain ###. I have the data formatted as General and Dur in the
PT is formatted as custom [h]:mm:ss
Any assistance with getting my data to show up as hh:mm:ss to replace the
### would be appreciated.

 

[Dave Peterson]

Negative values formatted as time will appear as ###.

You could use the 1904 base date
(Tools|Options|calculation tab|check "1904 date system")

Be aware that your dates will now be off by 4 years and one day.

And copying data (dates and times) between workbooks with different base dates,
will be a big problem.

Saved from a previous post:

One way to add (or subtract) those four years back is to find an empty cell, put
1462 into that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (or
subtract) (in theoperation box) and check values.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

My data has a col called Dur which is a calculation subtracting one time from
another time. Depending on my filtering in the pivot table depends on how
many cells contain ###. I have the data formatted as General and Dur in the
PT is formatted as custom [h]:mm:ss
Any assistance with getting my data to show up as hh:mm:ss to replace the
### would be appreciated.

 

[Luke M]

Is the ### due to insufficient column width, or a negative time? If the
former, you'll just need to increase the width. If the latter, you need to
adjust your formula, or change the format, as XL can't display negative times.