[nighttrain]

On my computer the regional settings of date are:

yyyy-MM-dd

In sheet the column 3 with with "dates" looks like:

03.08.2012
04.08.2012
05.08.2012
........

So firstly before doing something operations on it or compare those dates with actual date i wanted to convert it to proper format like is on my compuer (yyyy-MM-dd) so i tried to use: Convert.todate, cdate and format() but with no success. I tried firstly

Convert.todatetime(Cells(x,3))

and after that would like to change format by use Format(), but i couldnt do the above first to get all those dates and convert to date/ How should i do this?

[CharlieMay]

Instead of format, Try:

Convert.ToDateTime(Cells(x,3)).ToString("yyyy-MM-dd")

[nighttrain]

I will try it as soon as possible, but i wonder if it will work, so when i give this macro to my colleague- and his regional of date is dd.MM.yyyy my macro will work on his computer too?

P.S WHy i should not to use Format()?

This post has been edited by nighttrain: 07 September 2012 - 10:28 PM

[nighttrain]

ok i could do that :

Convert.ToDateTime(Cells(x,3)).ToString("yyyy-MM-dd")

But only when it contains date format, which is possible to convert to toher date format, what if this is upossible? ISDate says that it cannot be converted, so what i should to do in that case?

[CharlieMay]

Instead of IsDate, you should use DateTime.TryParse and determine from there if you can convert or not. Upon successful parse you could then take the resulting value and format it with the various methods that datetime offers.

  Dim dt As Date
  Dim SomeDateString As String = "01.02.2012"
  If DateTime.TryParse(SomeDateString, dt) Then
    MsgBox(dt.ToString("dd/MM/yyyy"))
  Else
    MsgBox("Not a valid date")
  End If

[Sunny.W]

sheet.Range["A1:A3"].NumberFormat = "dd/mm/YYYY";

This sentence is based on a .NET Excel component.

[nighttrain]

Its not working:

first Date As String
secondDate As String
'01 - day ,   09 - month  ,  2012 - year
firstDate = "01-09-2012 15:21:44"
'28 - day ,   08 - month  ,  2012 - year
secondDate = "28-08-2012 15:13:53"

                Dim a As Date
                Dim b As Date

                a = Convert.ToDateTime(firstDate.ToString("dd-MM-yyyy hh:mm:ss"))
                b = Convert.ToDateTime(secondDate.ToString("dd-MM-yyyy hh:mm:ss"))
                'Datediff return Long Type
                msg2 = DateDiff(DateInterval.Minute, b, a)
                Console.WriteLine(msg2 / 60)
                Console.ReadLine()

I receive error:

Unable to cast object of type String to System.IFormatProvider

at those lines:

 a = Convert.ToDateTime(firstDate.ToString("yyyy-MM-dd"))

This post has been edited by nighttrain: 13 September 2012 - 05:53 AM

[torind_2000]

your first line is wrong.

[nighttrain]

its litteral wrong.

[CharlieMay]

OK, here's a little help. I've chosen a date that can't be confused as a month.

Dim strDate as String = "29/06/2011 14:23:32" 'so we know that this is June 29th, 2011 at 2:23:32 PM
'Two things here, in the format MM represents month, not mm and since we're using 24hour time, HH is used in place of hh

Dim DateParsed as DateTime = DateTime.ParseExact(strDate, "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) 'So the kicker here is what you have in the " " must match the format you are trying to parse. Once that's successful, you can format it as needed

'for example:
MessageBox.Show(DateParsed.ToString("dd MMM yyyy hh:mm:ss tt")) ' 29 Jun 2011 02:23:32 PM
MessageBox.Show(DateParsed.ToString("MM/dd/yyyy")) '06/29/2011
MessageBox.Show(DateParsed.ToString("MMM dd, yyyy"))'Jun 29, 2011
MessageBox.Show(DateParsed.ToString("ddd, MMM dd, yyyy HH:mm:ss)) 'Wed, Jun 29, 2011 14:23:32

Now that being said, this should get you started. Since you are pulling in results that are consistant it should work. BUT when validating input against the user you are going to want to protect yourself from entries like 29.6.2011 or 29-6-2011 as the parse above will fail on that since it's not an exact format match.

This post has been edited by CharlieMay: 13 September 2012 - 09:49 AM

[nighttrain]

First case:
Ok so in my understanding this bold format:

Dim DateParsed As DateTime = DateTime.ParseExact(strDate, [b]"dd/MM/yyyy HH:mm:ss"[/b], System.Globalization.CultureInfo.InvariantCulture)

has to be the same as input string right?:

Dim strDate As String = "29/06/2011 14:23:32"

and then i could use any of possible to formats e.g:

Console.WriteLine(DateParsed.ToString("dd MMM yyyy hh:mm:ss tt")) ' 29 Jun 2011 02:23:32 PM
        Console.WriteLine(DateParsed.ToString("dd/MM/yyyy")) '29/06/2011
        Console.WriteLine(DateParsed.ToString("MM/dd/yyyy")) '06/29/2011
        Console.WriteLine(DateParsed.ToString("dd-MM-yyyy")) '29-06-2011
        Console.WriteLine(DateParsed.ToString("MMM dd, yyyy")) 'Jun 29, 2011
        Console.WriteLine(DateParsed.ToString("ddd, MMM dd, yyyy HH:mm:ss"))

Correct me if i am wrong.

Second case:

Why those:

Console.WriteLine(DateParsed.ToString("dd/MM/yyyy")) '29/06/2011
        Console.WriteLine(DateParsed.ToString("MM/dd/yyyy")) '06/29/2011

gives me:

29-06-2011
06-29-2011

Third case:

What's in case that e.g in excel file in 1 column i got thousand of rows with diffrent formats of date (strings) e.g:

19.09.2011
06/29/2011
23/01/2012
13-05.2010
....

How to handle that? If i would like to take each of that strings and compare e.g with Now() e.g strings are in column 3: [ if Cells (x,3) > Now() ]or something how to always format that strings, whats the best way, because your way is for one input string format on start...

This post has been edited by nighttrain: 13 September 2012 - 01:08 PM

[CharlieMay]

That issue with the different formation is when I would think using TryParseExact would work for you.

With TryParseExact, you can check against both formats and upon success of any of them you would still end up with a date that you could format as above.

[nighttrain]

Could you give an example on my case of that? And whats in case i have a lot of diffrent strings in excel column how to face that?

[nighttrain]

Whats in case if in column in my excel sheet are a lot of diffrence strings with dates e.g:

23-09-2012
29/07/2012
05/31/2011
2012-03-20
28.02.2011

how to face that issue when i am looping through that rows and compare e.g to Now()?
example:

Dim dt As Date
Do
dt = ???????????????????DateTime.Parse(Cells(x,1)) Then

if dt > Now() The
...
end if
Loop Until Cells(x,1) = Nothing

This post has been edited by nighttrain: 13 September 2012 - 02:05 PM