How to find first date of month for dataset in vba
I have a dataset as such:
05/06/2015 04/06/2015 03/06/2015 02/06/2015 29/05/2015 28/05/2015 27/05/2015 26/05/2015 25/05/2015 22/05/2015 21/05/2015 20/05/2015 19/05/2015 18/05/2015 15/05/2015 14/05/2015 13/05/2015 12/05/2015 11/05/2015 08/05/2015 07/05/2015 06/05/2015 05/05/2015 04/05/2015 30/04/2015 29/04/2015 28/04/2015 27/04/2015 24/04/2015 23/04/2015 22/04/2015 21/04/2015 20/04/2015 17/04/2015 16/04/2015 15/04/2015 14/04/2015 13/04/2015 10/04/2015 09/04/2015 08/04/2015 07/04/2015 06/04/2015 02/04/2015 01/04/2015 31/03/2015
They refer to weekdays of the year. Among these dates, I want to find the first date of the month. So, for example, in the month of June, the first date to be determined should be 02/06/2015 and for May before 04/05/2015.
I've tried something like this:
=IF(Cellreference=((Cellreference)-DAY(Cellreference)+1),TEXT(Cellreference,"mmm"),"")
But it will only take the first date of the month. I want this to be the first date of the month in the list of dates.
You need to be guided by how to achieve this.
+3
source to share
1 answer
I can see that you are using Formula, so I can only suggest below:
=MIN(IF(MONTH(AllDateReference)=MONTH(CellReference),AllDateReference,1E+99))
This is an array formula entered with Ctrl+Shift+Enter.
Actual sample:
=MIN(IF(MONTH(A$1:A$46)=MONTH(A1),A$1:A$46,1E+99))
where A$1:A$46
contains all your dates.
Edit1: Show only the first date of the month.
=IF(A1=MIN(IF(MONTH(A$1:A$46)=MONTH(A1),A$1:A$46,1E+99)),A1,"")
Example:
+5
source to share