Custom date - pivot table data shows 0 days, yet chart is displaying this as 30days

Without sample (i.e. underlying data and how each field is set) bit hard to help you.

But issue likely is due to lack of duration data type in Excel.

Meaning, the value is treated as datetime value, and since Excel's earliest date is 1/1/1900... It has issue dealing with 1/0/1900 (12/31/1899).

In value field, it's interpreted as 1/0/1900, but in pivot chart, it's interpreted as 12/31/1899. Hence, 30/31 in your label.

Best way to deal with this, is to use different format. Ex. [h]:mm or as 0.00 where integer value shows # of days, and decimal portion represents time value.

Unfortunately, Excel's sheet or data model can't deal with duration data type.

d in custom formatting stands for DAY() i.e. Day of month and not # of days.