Excel Table Date Format using Sharepoint List & Flow

Question

Hello,

Hoping someone can help me.

I'm using Office 365, Excel Online for Business and need to set up a spreadsheet which will return an age of a learner on a specific date (31/08/2019).

I've never had a problem using this formula until we've converted to Office 365 - I now receive a #value return in the cell that is meant to show the age.

Although my Office 365 is set up to UK timezone it still seems to convert all dates to the MM/dd/yyyy format - I need it to show dd/MM/yyyy so i've changed the format of the cell but you can see from the attached photo that the input is still mm/dd/yyyy

• could this be the issue?

The formula entered in the age cell is =DATEDIF(E4,$F$4,"Y")

I have a flow linked to this spreadsheet where it adds and updates data from a Sharepoint List. As Sharepoint List setup won't allow me to add a date format as dd/mm/yyyy - i've added them as mm/dd/yyyy (i also tried short text) & then used a Convert Date and Time function in the flow - however, when the date arrives at excel it still appears to convert to mm/dd/yyyy.

Answer 1

Hi Lisa,

I notice that the date in E4 is placed at the left side of the cell, which means it's not recogised by Excel as a date. Based on my test, this is because the region of the site where the workbook is stored (OneDrive for Business or SharePoint Online) isn't English (United Kingdom).

To change the regional settings and fix this, you may refer to the following steps:

If the workbook is stored in your OneDrive for Business:

1. Click the gear icon on OneDrive for Business > OneDrive Settings;
2. Click More Settings > Regional settings > Locale > switch to English (United Kingdom) > click the OK button.

If the workbook in stored in a SharePoint Online site: 

1. Click the gear icon > Site information > View all site settings
2. Under "Site Administration", click Regional Settings > in Locale, switch to English (United Kingdom) > click the OK button.

Please let us know if there is any update. 

Best Regards,

Nathan

Answer 2

Thank you Nathan - solved