Skip to main content

1904 issue when data is extracted from the SharePoint list.

We have an option in SharePoint to export the SharePoint list to an excel. 

When a user extracts a report and enable the "user 1904 date system" under Excel Options-> Advanced->When calculating this workbook,  the data columns in the excel would get added by 4 years and 1 day.

For example: If the date column has the value 08/03/2019, it would get converted to 09/03/2023.

Please click here to know more about the excel date systems.


Please follow the steps to correct the shifted dates!

Correcting Shifted Dates

If you link from or copy dates between workbooks, or if you change the date system for a workbook that already contains dates, the dates may be shifted by four years and one day. You can correct shifted dates by following these steps:
1.    In an empty cell, enter the value 1462.
2.    Select the cell. On the Edit menu, click Copy.
Note In Excel 2007, select the cell, and then click Copy in the Clipboard group on the Home tab.
3.    Select the cells that contain the shifted dates. On the Edit menu, click Paste Special.
Note In Excel 2007, select the cells that contain the shifted dates, click Paste in the Clipboard group on the Home tab, and then click Paste Special.
4.    In the Paste Special dialog box, click to select the Values check box under Paste, and then click to select either of the following check boxes under Operation. |Select this| If| |---|---| |Add| The dates must be shifted up by four years and one day.| |Subtract |The dates must be shifted down by four years and one day.|
5.    Click OK.

Repeat these steps until all the shifted dates have been corrected.


Comments

Post a Comment

Popular posts from this blog

"An unexpected error has occurred. Changes to your data cannot be saved" excel SharePoint

“If you’re not making some notable mistakes along the way, you’re certainly not taking enough  business and career chances .” After creating a custom "Export to Excel" button on the SharePoint page as in my previous post:   https://nethrasb.blogspot.com/2020/05/export-to-spreadsheet-option-in.html we encountered an issue, where one of the user received "An unexpected error has occurred. Changes to your data cannot be saved" in the excel sheet to which the data has been exported. But I was able to export the data using "System Account" without any issue!  The user has full control over the site and there should not be any permissions related issues.  What could be the issue then?  It might seem silly but this is what happened. Humans make mistakes :) My fellow team mate who had been asked to create the view, created a "Personal" view instead of creating a "Public" view !  "Personal" view is i...
Post a Comment
Read more

Remove the page title from SharePoint online modern page

SharePoint Online supports two basic designs; The  “Classic”  and the  “Modern”  experience.  The “Classic” experience is a highly customizable user interface much similar to an On-Prem experience.   The “Modern” experience has some limitations on the design customizations unlike the "Classic" experience.  The "Modern" page can be created from Site Contents-> New->Page. The modern page would be created in edit mode where you can choose the page layout and add the necessary webparts and publish the page. But, how to remove the page title from the page where the customizations are limited? It's quite simple!!! 1. Open the page in the SharePoint designer -> "Advanced Edit Mode". 2. Look for the tag <mso:PageLayoutType msdt:dt="string">Article</mso:PageLayoutType>. Replace "Article" with "Home" as below and save the file. <mso:PageLayoutType msdt:dt="string">Home</mso:PageLayoutType> ...
Post a Comment
Read more

Create a Hyperlink column in SharePoint Online Custom List

“We would accomplish many more things if we did not think of them as impossible.” It is possible to create a hyperlink column in SharePoint list but is it possible to create a hyperlink column as a calculated column? The Answer is "Yesssssssssssssssssss". Following are the steps to accomplish the same. 1. Create a Calculated Column say URL with the formula to link the list column id to a page.  For example: ="http://sharepoint.com/sites/Test/Page.aspx?ID="&ItemID . 2.Create another calculated column say Info and refer the previously created calculated column here in anchor tag. For example:  =IF(ISBLANK(URL),"No URL",("<a href="&URL&" target='_blank'></a>"))  Make sure the data type returned in both the calculated columns are Numbers. Now its time to render the Calculated column as a HTML markup in SPO. 1. Ensure that the list is in the modern experience. 2. Click on the URL column-> Column settings-...
Post a Comment
Read more