Forum Discussion
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:
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?
This fixes the output but really ridiculous that I have to do this workaround.
formatDateTime(addDays(outputs('Compose'),1), 'M/d')
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.
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.This fixes the output but really ridiculous that I have to do this workaround.
formatDateTime(addDays(outputs('Compose'),1), 'M/d')
