Assign an index to each group identified by date

smide

Board Regular
Joined
Dec 20, 2015
Messages
162
Office Version
  1. 2016
Platform
  1. Windows
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.

AB
106.10.2019 - 22:151st
207.10.2019 - 21:001st
307.10.2019 - 22:001st
408.10.2019 - 22:002nd
509.10.2019 - 14:002nd
610.10.2019 - 17:002nd
705.10.2019 - 18:001st
810.10.2019 - 17:452nd

<tbody>
</tbody>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top