Select closest date to first of month

[Dennisklad1]

Hi and a happy new Year to all.

I have a record of files with created date metadate with the directory tool. Every month we have multiple files but I would like to select only the one closest to the first of the month.

Dates          Selected

01.03             true

05.02             false

30.01             true

22.01             false

05.01             false

02.01             true

[mbarone]

"Closest to the first of the month".  Which "first of the month"?  Meaning, relative to what month?  First that question has to be answered.  Once you have that answer, you can use the DateTimeTrim([month],'firstofmonth') function, and then use a DateTimeDiff function to calc the number of days between that first of the month and your value, and then take the minimum but using a summarize tool and join.

But first we'd need to know which "month" you want to get to the first of.

[Dennisklad1]

Ow sorry for the confusion. First of the month I mean the 01.** (**.01 for mm.dd format) of each month

[mbarone]

So if you have the following incoming dates:

1.  Dec 30 2020

2.  Jan 03 2021

3.  Dec 15 2020

4.  Nov 16 2020

Which first of the month do want to reference?

[pedrodrfaria]

Hi @Dennisklad1 

I have attached below a workflow that considers the closest date to the first of the month per each month.

Is this what you are trying to accomplish?

Pedro.

[Dennisklad1]

I'd like for every single month only one record that is the closest to the start of the month.

If I have:

01 Dec

03 Dec

25 Dec

then I would select 01 Dec and the same for all other months

[pedrodrfaria]

@Dennisklad1 

Is this what you are looking for?

[Ladarthure · Accepted solution]

If you want to calculate this, you can first use a DateTimeFormat([DATE],'%Y%m') which will give you a format like 202101 for this month, then sort yoiur data by this new field first and then by the dates itself.

And finally use a Sample tool to get the first record for each "period"

[echuong1 · Accepted solution]

I am a bit confused by your original example, but I believe I understand what you're looking for.

I started off by converting your values to actual date formats. This automatically made the dates in the year 1400, but the year doesn't matter that much in your case. I then derived the month from the date using a formula. I used a sort to sort by the month and date, so each group of months was together, and the dates were going in ascending order. This allowed me to use a sample tool to grab the first value per month, effectively being the date closest to the beginning of the month.

Hope this helps!