cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
utsav_ghosh1
Advocate II
Advocate II

Date conversion to dd/mm/yyyy

Hi All,

 

I have a column in sharepoint list of type date which i want to update with current date in NL foramt (dd/mm/yyyy).

 

when i use below function, it gives me the wrong data

DateValue(Text(Today()),"nl")  --> 8/11/2020

 

but if i change the language to en the date is correct but in mm/dd/yyyy format.

DateValue(Text(Today()),"en") --> 11/20/2019

 

How can i get the date in dd/mm/yyyy format ?

 

Regards,

Utsav

 

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User III
Super User III

Hi @utsav_ghosh1 

Can you show us the formula that you're using to update your SharePoint list? Are you calling the Patch function?

If so, you could just call Today() to return todays date. For example...

 

Patch(MySharePointList,
      Defaults(MySharePointList),
      {Title: "MyRecord", StartDate: Today()}
)

With regards to the original syntax...

DateValue(Text(Today()),"nl")

..this type of expression is redundant because in effect, we're calling Today() to return todays date, converting this to text with the Text function, and then converting that result back into a date with the DateValue function.

Therefore, my suggestion would be to update your SharePoint list with Today(), and then check the record that's been added/updated from within SharePoint. Is the record showing the correct date?

View solution in original post

7 REPLIES 7
mdevaney
Super User III
Super User III

@utsav_ghosh1 

Have you tried this approach already?

 

Text(Today(),"dd/mm/yyyy")

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

yes.. it is giving me wrong date

 

DateValue(Text(Today(),"[$-en-US]dd/mm/yyyy"))  --> 8/11/2020

@utsav_ghosh1 

This code...

Text(Today(),ShortDate,"nl")

 

...would appear to yield...

20-11-2019

 

...and this...

Substitute(Text(Today(),ShortDate,"nl"),"-","/")

 

...would get the desired format (although not a date).  So you could use the above for display purposes only (not to upload as a date to SharePoint).

20/11/2019

 

Can you work with this info?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

timl
Super User III
Super User III

Hi @utsav_ghosh1 

Can you show us the formula that you're using to update your SharePoint list? Are you calling the Patch function?

If so, you could just call Today() to return todays date. For example...

 

Patch(MySharePointList,
      Defaults(MySharePointList),
      {Title: "MyRecord", StartDate: Today()}
)

With regards to the original syntax...

DateValue(Text(Today()),"nl")

..this type of expression is redundant because in effect, we're calling Today() to return todays date, converting this to text with the Text function, and then converting that result back into a date with the DateValue function.

Therefore, my suggestion would be to update your SharePoint list with Today(), and then check the record that's been added/updated from within SharePoint. Is the record showing the correct date?

View solution in original post

mdevaney
Super User III
Super User III

 

@utsav_ghosh1 

I agree with @timl.  My initial thought was also that you should just PATCH using Today().  But it also seemed like you were just trying to get the date to display "properly" so my response was a reflection of that instead.

 

In any case, we'll all help you figure it out.

Hi @timl ,

Hi @utsav_ghosh1 

Thanks for clarifying that the problem is with the display of the date only. Is there any reason why you want to call DateValue?

 


@utsav_ghosh1 wrote:

yes.. it is giving me wrong date

 

DateValue(Text(Today(),"[$-en-US]dd/mm/yyyy"))  --> 8/11/2020


Can you clarify whether @mdevaney's worked for you (ie calling the Text function without DateValue)?

 

Text(Today(),"dd/mm/yyyy")

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,379)