Assign an index to each group identified by date

[smide]

Hello.


In column A (A1:A600) I have a dates and time in format dd.mm.yyyy - hh:mm (example. 07.10.2019 - 21:00).
Somehow I need to find an 'average' date for all those dates in column A (split data/dates in column A into two parts) and then to assign an index to each date (column A) according to the half it belongs.


If date from column A is in first half then index '1st', if in second index '2nd'.


All results should be in column B.


example.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]06.10.2019 - 22:15[/TD]
[TD="align: center"]1st[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]07.10.2019 - 21:00[/TD]
[TD="align: center"]1st[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]07.10.2019 - 22:00[/TD]
[TD="align: center"]1st[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]08.10.2019 - 22:00[/TD]
[TD="align: center"]2nd[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]09.10.2019 - 14:00[/TD]
[TD="align: center"]2nd[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]10.10.2019 - 17:00[/TD]
[TD="align: center"]2nd[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]05.10.2019 - 18:00[/TD]
[TD="align: center"]1st[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]10.10.2019 - 17:45[/TD]
[TD="align: center"]2nd[/TD]
[/TR]
</tbody>[/TABLE]

 

[jasonb75]

Should it be split by mean or median average?

In your sample both would give the same result, but with a different set of dates and times that may not be the case.

Are your dates actual valid dates formatted as shown, or are they text strings that look like dates?

 

[smide]

Should it be split by mean or median average?

In your sample both would give the same result, but with a different set of dates and times that may not be the case.

Are your dates actual valid dates formatted as shown, or are they text strings that look like dates?

Split by mean. All cells in column A are text cells (text strings that look like dates).

 

[jasonb75]

In that case, you're going to need a helper column to change the date to a valid format first. Based on your sample above, try this formula in B1 and fill down

=VALUE(SUBSTITUTE(SUBSTITUTE(A1,".","/"),"-",""))

Then this in in C1 and fill down

=IF(B1<AVERAGE($B$1:$B$8),"1st","2nd")

I don't think that you will be able to do it without the extra column unless you use a macro to convert the text dates to a valid format in the original column.