Unexpected Integers where dates should be with Excel Online (Business) connector in SharePoint Online

Importing an integer in place of a date in SharePoint Online and how to fix

BLUF: if(empty(items('Apply_to_each_5')?['DOB']), null, if(isInt(items('Apply_to_each_5')?['DOB']),addDays('1899-12-30', int(substring(items('Apply_to_each_5')?['DOB'], 0, 5))),formatDateTime(items('Apply_to_each_5')?['DOB'], 'yyyy-MM-dd'))) 

     If you've ever tried to import data from an excel file into SharePoint Online, M365, formerly O365, aka down for leap days, you may have run across weirdness when importing a date. Your source excel document may know that that column is a date, or it may thing it's a string. Regardless, when importing, it imports a number like 30008, 20701, etc. This will also cause your flow to fail if you're importing into a date column. 

I'll take you through all the steps (but if you just need the formula, it's above)

1. Add an Excel Online (Business) connector to list the rows in the Table




Note that the data must be within an Excel Table, not just an Excel Sheet. The easiest way to get a Table is to export your SharePoint List.

In this case, I'm watching for a new file to be dropped into a specific document library as my trigger to start. and Full Path is from that trigger.

Here's what this section looks like:

And inside Condition 13 (I know I should give meaningful names but then I'd just need to redact them before posting here) is where I add or update the SharePoint List Item.

Here's where the issue comes in.

Dates get converted to the number of days since December 30, 1899

And you thought Y2k was tricky. And it's some dates, not all. Some dates are fine. Why new year's eve eve of 1899?

I don't know if there's an answer for that, but when importing into a field where you want a meaningful answer, you need to account for

1> blank

2> null

3> integer

4> valid dates

Now here's the good stuff:

if(empty(items('Apply_to_each_5')?['DOB']), null, if(isInt(items('Apply_to_each_5')?['DOB']),addDays('1899-12-30', int(substring(items('Apply_to_each_5')?['DOB'], 0, 5))),formatDateTime(items('Apply_to_each_5')?['DOB'], 'yyyy-MM-dd'))) 

if DOB is empty, the formula returns null, if DOB is an Integer, it adds that number of days to 12/30/1899, and if it's neither of those, a real date is assumed and it formats it how you like.

Hope this helps. Feel free to ask questions below.

Comments

Post a Comment

Popular posts from this blog

Setting SharePoint announcements to auto delete after expiration

Image
Setting announcements to auto delete after expiration One thing that always bugged me was the expiration date attached to an Announcement list that does nothing out of the box. Sure, you could always add a filter and change the default view but as soon as you add an Announcements web part to a page the view is UNFILTERED even if a filtered view is the only one to choose from.  You or I could change the default view, but we can’t trust all users to do so. A workflow would be fine to delete the item – simply pause until the expire date then delete the current item.  The workflow would stick with the template if you saved your list off as a template and then still work when you created a new list from that template.  That’s good, but still relies on the user creating the right Announcement list. The better answer was to change the Announcements default content type.  If anyone asks, tell them it’s called Governance, Sparky. The first step was to go to site sett...
Read more

Updating a single field in a SharePoint List using Power Automate Flows

Image
  Updating a single field in a SharePoint List using Power Automate Flows Updating a single field in a SharePoint List using Microsoft Flows (aka Microsoft Power Automate) can be a bit tricky with longer lists with lots of required fields and drop downs possibly resetting your data. The work around is code, but if there are non-developers creating flows, then this process is even more cumbersome. As a result, the Flow below will do the heavy lifting using a separate list and then anyone can populate that list from a different Flow when such a thing is needed. First step: create the list Here’s the input needed in this list. List: The name of the list SiteLink: The link to the site (trim off /lists/xyzlist) IDFromSource: This is the internal ID of the item to update FieldToUpdate: The name of the field you want to update. This is case sensitive. ValueForField: What you want to set it to. Next, create the Flow. Here’s what it will look like   When a Shar...
Read more

SharePoint driven rich text dashboard using jqueryui. (JQuery file)

Jquery file for Operations Dashboard Download this file <script type="text/javascript"> $(document).ready (       function()       {             SP.SOD.executeFunc('sp.js', 'SP.CamlQuery', RetrieveListValues);       } ); function RetrieveListValues() {       var query = new SP.CamlQuery(); query.set_viewXml("<View><Query><OrderBy><FieldRef Name=\"SortOrder\"/></OrderBy></Query></View>");                                     var clientContext = new SP.ClientContext("https://sharepoint.name/section/OD");       var oList = clientContext.get_web().get_lists().ge...
Read more