0

I have dates in column A that are mm/dd/yyyy. I need to now convert the date from column A into yyyy;mm;dd for the DATE function. Below is what I have to get the data I want. Currently it is hard coded. I need the 2011;12;30 to be sucked in from A2 which is 12/30/2011. Then I need to add one more day to it for the second date (2011;12;31). Any ideas?

=COUNT(FILTER( 'Sheet 1'!O:O ; 'Sheet 1'!O:O >=DATE( 2011;12;30) ; 'Sheet 1'!O:O <=DATE( 2011;12;31) ; SEARCH( "b" ; 'Sheet 1'!I:I ) ) )
user983223
  • 1,096
  • 2
  • 16
  • 31

1 Answers1

1

It is not clear what is in Date format, and what is in String format in your spreadsheet.

The simplest way to convert formats is to use YEAR, MONTH, DAY and other date functions.

For example to convert from the default (in US locale) format of mm/dd/yyyy to yyyy;mm;dd you can use the function:

=Concatenate(YEAR(A2),";",MONTH(A2),";",DAY(A2))

and copy this function to the fill down column A.

Guy
  • 11,039
  • 3
  • 41
  • 60