![]() |
### showing up in pivot table.
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. -- Thanks, Judy |
### showing up in pivot table.
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. Judy wrote: 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. -- Thanks, Judy -- Dave Peterson |
### showing up in pivot table.
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Judy" wrote: 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. -- Thanks, Judy |
All times are GMT +1. The time now is 03:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com