Beatrix
09-08-2013, 05:07 PM
Hi Everyone ,
I have a long list with dates and need to count the ones which are greter than 01/09/2012. However below formulas don't calculate it right:doh: I guess it's a format issue as I converted it from 2011-Sep-15 12:00:00 to 15/09/2011 by using the VBA code below. I attached a sample file. Any suggestions to fix the date issue??: pray2:
Cheers
Yeliz
=COUNTIFS(Data!$AK$2:$AK$25452,"="&$B11,Data!$C$2:$C$25452,"="&$B$2,Data!$T$2:$T$25452,">01/09/2012")
=COUNTIF(T2:T25452,">01/09/2012")
Sub ConvertDates()
Dim Old As Range
Dim Cel As Range
Set Old = Sheets("Data").Range("T2:T" & Cells(Rows.Count, 1).End(xlUp).Row)
'Range formating required to fix some goofy results.YMMV
'Try it with nd without the next 4 lines
With Old
.NumberFormat = "dd/mm/yyyy"
.HorizontalAlignment = xlLeft
End With
'2011-Sep-15 12:00:00 to 15/09/2011
For Each Cel In Old
Cel = Format(Mid(Cel, 6, 3) _
& "/" & Mid(Cel, 10, 2) _
& "/" & Left(Cel, 4), "dd/mm/yyyy")
Next Cel
End Sub
I have a long list with dates and need to count the ones which are greter than 01/09/2012. However below formulas don't calculate it right:doh: I guess it's a format issue as I converted it from 2011-Sep-15 12:00:00 to 15/09/2011 by using the VBA code below. I attached a sample file. Any suggestions to fix the date issue??: pray2:
Cheers
Yeliz
=COUNTIFS(Data!$AK$2:$AK$25452,"="&$B11,Data!$C$2:$C$25452,"="&$B$2,Data!$T$2:$T$25452,">01/09/2012")
=COUNTIF(T2:T25452,">01/09/2012")
Sub ConvertDates()
Dim Old As Range
Dim Cel As Range
Set Old = Sheets("Data").Range("T2:T" & Cells(Rows.Count, 1).End(xlUp).Row)
'Range formating required to fix some goofy results.YMMV
'Try it with nd without the next 4 lines
With Old
.NumberFormat = "dd/mm/yyyy"
.HorizontalAlignment = xlLeft
End With
'2011-Sep-15 12:00:00 to 15/09/2011
For Each Cel In Old
Cel = Format(Mid(Cel, 6, 3) _
& "/" & Mid(Cel, 10, 2) _
& "/" & Left(Cel, 4), "dd/mm/yyyy")
Next Cel
End Sub