Forum Discussion

rwittels's avatar
rwittels
Copper Contributor
Feb 02, 2021
Solved

Sharepoint list creation from Excel breaks my dates

I am trying to create a list on Sharepoint to be used in Power Automate. My Excel list has the dates column formatted as date only and as yyyy/mm/dd:   When I create a new Sharepoint list fro...
excel
SharePoint List
  • rwittels's avatar
    rwittels
    Feb 08, 2021

    This fixes the output but really ridiculous that I have to do this workaround.

    formatDateTime(addDays(outputs('Compose'),1), 'M/d')

     

rwittels's avatar
rwittels
Copper Contributor
Feb 08, 2021

Although the JSON corrects the way dates are displayed in my SharePoint list, Power Automate matches dates in my list minus one day against today's date. This is so confusing!

 

For example my SharePoint list has the items:

User1 with birthday date 2/8/1985

User2 with birthday date 2/9/1970

(dates are formatted as M/d/yyyy)

 

My flow has a Apply to each loop with this condition to find list dates with today's date:

 

formatDateTime(outputs('Compose'), 'M/d')

 

is equal to

 

formatDateTime(utcNow(),'M/d')

 

Matching dates append the Title field (User1, User2 etc.) to a string variable which is then used in an email.
 
If today is 8 February, my flow should return Titles with dates matching today, such as User1. But it is returning User2!
 
So, the date fix seems to be cosmetic and does not actually fix the underlying issue of SharePoint removing a day from each date.
 
Right now, the only way my outputs work is if I add one day to each date in Excel and then import the list without doing the JSON formatting. The dates appear wrong in SharePoint but my flow matches dates correctly.
 
Any help with this will be appreciated.
rwittels's avatar
rwittels
Copper Contributor
Feb 08, 2021

This fixes the output but really ridiculous that I have to do this workaround.

formatDateTime(addDays(outputs('Compose'),1), 'M/d')

 

  • blountkr's avatar
    blountkr
    Copper Contributor
    Nov 15, 2021

    rwittels was this 

    formatDateTime(addDays(outputs('Compose'),1), 'M/d')

    something you entered within the JSON script area?  I'm having the same issue, and I do not know where in the LIST you entered this for it to correct the missing date.

    • rwittels's avatar
      rwittels
      Copper Contributor
      Nov 15, 2021
      Hi. Don't worry about the JSON. I import my list and leave it with one day missing on each date. I correct the dates in my automation with the formatDateTime line.
      • SergDol's avatar
        SergDol
        Copper Contributor
        Nov 18, 2021

        rwittels I also faced the same issue and had to change dates in the source excel file before import to the SharePoint list

Resources