Unanswered

Different date formats issue

(0) Share

Report

Posted on by RG-03060625-0

A team member has entered multiple date fields in SharePoint, some as text and others as date type. Even though for text fields I’ve used Text(receiving_date_var, "[$-en-US]dd-mmm-yyyy") everywhere, and for date fields I’ve set the format to dd-mmm-yyyy, the saved formats are still inconsistent. How can this be resolved?

Categories:

General Questions

All responses (3)

Answers (0)

MarkRahn 1,049 Super User 2025 Season 1 on at

Like (0)

Report

Different date formats issue

As @WarrenBelz has pointed out, SharePoint stores dates as UTC in the background. You are going to be fighting this continually with this data.

How far along are you? Can you correct the data now?

Either by adding a new Column with the correct format and editing the List in Grid View or Exporting the data to Excel and importing into a new List with the correct formats? Another option is to create a Power Automate Flow to get the values for these text fields and writing the correct value in UTC to new DateTime Columns added to your List.

You will be in a constant struggle between the text and the DateTime fields unfortunately. The best thing to do if you are able is to correct things and let SharePoint manage the DateTime values.

This community is supported by individuals (including the awesome Warren Belz) freely devoting their time to answer questions and provide support. They do it to let you know you are not alone. This is a community.

If someone has been able to answer your questions or solve your problem, please click Does this answer your question. This will help others who have the same question find a solution quickly via the forum search.

If someone was able to provide you with more information that moved you closer to a solution, throw them a Like. It might make their day. 😊

Thanks
-Mark
WarrenBelz 147,570 Most Valuable Professional on at

Like (0)

Report

Different date formats issue

Hi @RG-03060625-0,

SharePoint stores dates "in the background" with UTC time in ISO 8601 format yyyy-MM-ddTHH:mm:ssZ with T seperating date and time and Z being the Time Zone. The Regional Settings in the SharePoint site (if you view it there) then displays the date in the site's chosen format in the site's time zone, so whatever date values you are sending back to a date field ends up stored this way.

When you get it back in Power Apps, it likewise converts to the device's time zone in the format that you specify.

If you store a date in Text format, there is only one conversion - Power Apps will use DateValue() to change it back to a date and then in the format you want to display (it is never a date in SharePoint)

So my questions are - what inconsistencies are you encountering and (more importantly) why are you storing dates as Text in SharePoint as this will only impose restrictions in other areas (particularly filtering) that you can really do without.
MS.Ragavendar 2,185 on at

Like (0)

Report

Different date formats issue

@RG-03060625-0,

Is the backend column in the sharepoint is not a datetime column ?