Convert date to dd/mm/yyyyy

Help
1

Excel stores values under this format MM/dd/yyyy HH:mm:ss, but i need to use a variable which represents cell in Excell and the desired format should be “dd.mm.yyyy”.
FIrst,I tried using regular expression and get only the date (without HH:mm:ss) and having this match i convert it to string.Now I’m looking for ideas to have this string repsented in this way “dd.mm.yyyy”.

I can not use DateTime.Parse exact, or convert it to date due to compiler.
I tried converting string to int32 using Cint in order to utilize mentioned methods, but alas it doesn’t work.

Any suggestions?

2

Hi Kuzinyd,

Have you tried using: YourVariable.ToString(“dd.MM.yyyy”) ?

3

Fine
hope this expression would help you
if we are iterating through excel with read range activity and using a for each row loop then
inside the loop use a assign activity like this
str_datevalue = Datetime.ParseExact(row(“yourcolumnname”).ToString.SubString(0,10),“MM/dd/yyyy”,system.globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

now the string variable str_datevalue output would be in format dd.MM.yyyy

Cheers @kuzinyd

4

Hi @Karsten_Bertelsen,
I did, in fact, compiler warns that option strict on doesn’t allow implicit conversion from str to int32

5

Hi @Palaniyappan,
Could you elaborate on .SubString method?

6

usually when a datetime is read in excel it will give us output in a string of format
dd/MM/yyyy hh:mm:ss
so to take the first 10 characters alone in that string
used .SubString(0,10)
where 0 is the index position from where to start denoting the first position
and 10 represents the number of characters to be obtained which would give us dd/MM/yyyy

Cheers @kuzinyd

7

@Palaniyappan
If: The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.

image
image1147×279 16.6 KB

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.