How to identify the 1st day of the month in a given dataset in Numbers

I thought of a different way that does not require the dates to be in any particular order, does not require checking for the row, does not reference any specific cell in another row (only the entire column), there can be gaps in the data, and you can add/delete/move rows (in case you missed entering a day or entered one twice or out of order) without it breaking the formula. One formula is for the earliest date of the month, one for the latest day of the month, and one that does both.

First date:

C2 =IFS(B2="","",B2=MINIFS(B,B,">"&EOMONTH(B2,−1),B,"<="&EOMONTH(B2,0)),"First day of month",TRUE,"")

Last Date:

D2 =IFS(B2="","",B2=MAXIFS(B,B,">"&EOMONTH(B2,−1),B,"<="&EOMONTH(B2,0)),"Last day of month",TRUE,"")

Both combined (if a date is both first and last, the result will be "first"):

C2 =IFS(B2="","",B2=MINIFS(B,B,">"&EOMONTH(B2,−1),B,"<="&EOMONTH(B2,0)),"First day of month",B2=MAXIFS(B,B,">"&EOMONTH(B2,−1),B,"<="&EOMONTH(B2,0)),"Last day of month",TRUE,"")

IFS is like a collection of if then else statements. IFS(first condition, do this if TRUE else continue to next one, second condition, do this if TRUE else continue to next one, and so on)

For the first day of the month it finds the minimum date that is > than the last day of the previous month and <= the last day of the given month. If the date is the same as that minimum date then it is the first day of that month.

For the last day it finds the maximum date that is > than the last day of the previous month and <= the last day of the given month. If the date is the same as that maximum date then it is the last day of that month.

The initial test for ="" is for if the cell is blank. The rest of the formula does not work correctly on blank cells.

If the dates are sorted and no gaps (empty rows) between dates,

C2 =IFS(ROW()=2,"First day in the column",MONTH(B2)>MONTH(B1),"first day of month "&MONTH(B2),TRUE,"")

Fill down from there

The meat of the formula is the test for if MONTH(B2)>MONTH(B1).

The test for ROW()=2 is because there is text (not a date) in the previous row and that would cause an error.

The TRUE is like "else". If not row 2 and the month is not greater than the row above then the result is ""

Using MONTH on an empty cell returns 0. Using it on text returns an error.

Could you also help with Last day of the various months. I tried understanding the logic but could not.

Badunit's creative solution is based on checking two adjacent cells and comparing the MONTH() of their values.

Simplifying (ignoring) the 'row 2' check for now, it's like saying:

If (MONTH(B3) is different from MONTH(B2)) then we know B3 is the first entry in a new month.

In this case, it's checking if the MONTH() of the cell above is different from the MONTH() of the current cell. If it is, we know we've crossed a monthly border.

To get the last entry of a month, it's basically the same thing, namely if the MONTH() of the current cell is different from the MONTH() of the next (instead of previous) cell:

=IF(MONTH(B3)<MONTH(B4),"LAST day of the month","")

Additionally, the way IF() works is that you have a check condition, then the action to take if the condition is TRUE, then the action to take if the condition is FALSE.

So now you just need to combine the first-of-month and last-of-month checks so that the last day of the month calculation is run if it's not the first day of the month:

For example:

=IF(MONTH(B4)>MONTH(B3),"FIRST day of the month",IF(MONTH(B4)<MONTH(B5),"LAST day of the month",""))

This checks cell B4 to the cell above (B3) to see if it's the FIRST day of the month, if that's false we check it again against the cell below (B5) to see if its the last.

Note this all assumes the data are in chronological order :)

Is it only one month of data or are there multiple months in this column of data and you want to identify the earliest day of every month?