Convert text to date and save it in Sharepoint list

[Lukas1]

Hi,

I am trying to convert a string to a date-variable that is accepted by sharepoints date-format:

I have a string-variable:

Set(varDateEN;"12/31/2020")

I convert this string into date-format: 

Date(Value(Right(varDateEN;4));Value(Left(varDateEN;2));Value(Mid(varDateEN;4;2)))

Then I update an item in a sharepoint list including the date value. All values of the item are being updated, except the date.

Any ideas what's going wrong? Is there an easier way to convert text to date?

Thanks,

Lukas

[WarrenBelz · Accepted solution]

 @Lukas1 ,'Have you tried simply

DateValue(varDateEN)

 as per DateValue function 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Lukas1]

@WarrenBelz , yes I tried. Doesn't work either.

[WarrenBelz]

Hi @Lukas1 ,

Actually you should need

DateValue(varDateEN,"en-US")

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Lukas1]

@WarrenBelz , thanks a lot for your help!

It is still not working. Please have a look at the preview below, I would expect it to be "7/22/2020", but instead it is "22.7.2020":

I also tried this without success:

DateValue(varDateEN;"mm/dd/yyyy")

[WarrenBelz]

@Lukas1 ,

What regional code do you have on your SharePoint server? It will convert a date to the local format (and I can see this on the bottom of your code). The fact it converts and shows 22.7.2020 means it is a valid date on the source (there are not 22 months in a year).

This is also the reason for my first post (you do not need to specify the format if it matches the local format). You cannot store 07/22/2020 format as a date if the source is dd.mm.yyyy

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Lukas1]

@WarrenBelz That's it! I was confused because the dates in my sharepoint list are shown in the US-date-format:

Anyway, DateValue() with a german date-format is doing the job now.

Thanks again.