Changing Date Format for SharePoint in Excel

When dealing with dates in SharePoint that are not read correctly by the GETPIVOTTABLE function, you may need to transform the date format to "ddd m/d/yyyy" for proper functionality. Below is an Excel formula that can help you achieve this.

Excel Function: Change Date Format

Function Overview

This function converts a date from the standard Gregorian format to a custom format "ddd m/d/yyyy".

Function Definition

=TEXT(A1, "ddd m/d/yyyy")

Parameters

• A1: The cell containing the date that you want to format.

Return Type

• Returns a string formatted as "ddd m/d/yyyy".

Usage Example

Consider you have a date in cell A1 as "10/05/2023" (October 5th, 2023). Here’s how to change its format:

1. In cell B1, enter the formula:

   =TEXT(A1, "ddd m/d/yyyy")

2. Press Enter.
3. The output will display as "Thu 10/5/2023".

Implementation Steps

1. Open your Excel sheet.
2. Identify the cell (e.g., A1) that contains the date to be reformatted.
3. Select another cell (e.g., B1) for the output.
4. Insert the formula as shown above.
5. Copy and drag the formula down if you have multiple rows of dates.

Considerations

• Ensure that the date is actually recognized as a date by Excel. If the value is text, you may need to first convert it using the DATEVALUE function.
• If you are planning to use this in a SharePoint list, ensure that the SharePoint column type for the date is set up to recognize the correct format after you apply the transformation.

Conclusion

This method effectively prepares your dates for compatibility with the GETPIVOTTABLE function or any other calculations that may require a specific date format. By following the steps outlined above, you can ensure your SharePoint dates are read correctly in Excel.

Further Learning

For more advanced Excel functions and data analysis techniques, consider exploring courses on the Enterprise DNA Platform that focus on Excel, data modeling, and business intelligence.