Help :( Date format causing issues with my Pivot Table/Calculated Field

[SarahManihera]

Hello,
Hoping you can help. I've created a pivot table with calculated fields, where the fields a sum of raw data within date ranges.
It works fine from my end, however when I save it to a shared drive, the date format in the raw data changes from dd/mm/yyyy to d/mm/yyyy. This is effing up my calc fields, because my calcs are looking for dd not d.

I've tried converting the date to TEXT, no luck.
I've tried formatting the cells to Custom dd/mm/yyyy and while it changes the output to look correct (i. 01/02/2017), the calc fields still don't work because the original value in the formula bar is still 1/02/2017.

The weird part is, the issue is only happening to SOME people, not all. Which makes me think there is a default format setting somewhere that some of us have, and some of us don't. I've never come across this issue before so could be a rookie error on my part, but hoping someone can let me know how to fix.

Here is the formula list for some of the Calc Fields I've created, as you can see, all dates less than the 10th have errored out, because the calc fields are referencing 01/02/2017 but the raw data date has changed to 1/02/2017:
[TABLE="width: 847"]
<tbody>[TR]
[TD]Field[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]01/02/2017 - 05/02/2017[/TD]
[TD]=#NAME?+#NAME?+#NAME?+#NAME?+#NAME?[/TD]
[/TR]
[TR]
[TD]06/02/2017 - 12/02/2017[/TD]
[TD]=#NAME?+#NAME?+#NAME?+#NAME?+'10/02/2017'+'11/02/2017'+'12/02/2017'[/TD]
[/TR]
[TR]
[TD]13/02/2017 - 19/02/2017[/TD]
[TD]='13/02/2017'+'14/02/2017'+'15/02/2017'+'16/02/2017'+'17/02/2017'+'18/02/2017'+'19/02/2017'[/TD]
[/TR]
[TR]
[TD]20/02/2017 - 26/02/2017[/TD]
[TD]='20/02/2017'+'21/02/2017'+'22/02/2017'+'23/02/2017'+'24/02/2017'+'25/02/2017'+'26/02/2017'[/TD]
[/TR]
[TR]
[TD]27/02/2017 - 05/03/2017[/TD]
[TD]='27/02/2017'+'28/02/2017'+#NAME?+#NAME?+#NAME?+#NAME?+#NAME?[/TD]
[/TR]
[TR]
[TD]06/03/2017 - 12/03/2017[/TD]
[TD]=#NAME?+#NAME?+#NAME?+#NAME?+'10/03/2017'+'11/03/2017'+'12/03/2017'[/TD]
[/TR]
[TR]
[TD]13/03/2017 - 19/03/2017[/TD]
[TD]='13/03/2017'+'14/03/2017'+'15/03/2017'+'16/03/2017'+'17/03/2017'+'18/03/2017'+'19/03/2017'[/TD]
[/TR]
[TR]
[TD]20/03/2017 - 26/03/2017[/TD]
[TD]='20/03/2017'+'21/03/2017'+'22/03/2017'+'23/03/2017'+'24/03/2017'+'25/03/2017'+'26/03/2017'[/TD]
[/TR]
[TR]
[TD]27/03/2017 - 02/04/2017[/TD]
[TD]='27/03/2017'+'28/03/2017'+'29/03/2017'+'30/03/2017'+'31/03/2017'+#NAME?+#NAME?[/TD]
[/TR]
[TR]
[TD]03/04/2017 - 09/04/2017[/TD]
[TD]=#NAME?+#NAME?+#NAME?+#NAME?+#NAME?+#NAME?+#NAME?[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 798"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Really appreciate any help anyone can offer!
Let me know if you need any further info.

 

[arthurbr]

Te NAME error usually indicates that XL does not recognize the function used ( e.g. SUMPORDUCT)

 

[etaf]

dates are just a number from 1900 each day is 1
so the format is how you see that date and should have nothing to do with a calculated field

what formula are you using - that looks for DD instead of D
DAY() should give you D or DD, but if you format the cell to DD you will get the 0 for single days
01, 02 , 03 etc