Sharepoint list creation from Excel breaks my dates

[rwittels]

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:

Excel Date Format

When I create a new Sharepoint list from Excel, the import screen shows the dates correctly:

But when the list is created, it minuses one day from the day of each date:

So when I go to column settings and change to date only, I am still missing a day on each date. Why is this happening?

Even if I format my Excel dates as M/d/yyyy h:mm AM/PM which is how it appears in the sharepoint list, it still breaks and removes a day. A workaround is to create a calculated column with this formula: 

=DATE(YEAR(Date),MONTH(Date),DAY(Date)+1)

but why should I have to do this?

[#2]

This JSON in Column settings -> Format this column -> Advanced mode fixed the day issue:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=toLocaleDateString(@currentField)"
}

 I guess this gave me an opportunity to learn about Date/Time fields.

[#3]

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.

[#4 · Accepted solution]

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

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

[#5]

@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.

[#6]

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.

[#7]

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

[#8]

I did that originally but decided that it was easier to leave my Excel file alone and just correct the imported dates with the addDays function in my code. It saves time.