Extracting month from a string of dates

[Rosa_Ramos]

Thanks in advance to anyone who even scans this query! :blush:

I have a rollup field that is flattening an array of dates - that’s working great! What I want to do is produce this same information showing only the month. For example, I have this list (a flattened list of dates from 3 different records showing only unique dates):

13/09/2021, 20/09/2021, 27/09/2021, 04/10/2021, 06/10/2021, 13/10/2021, 20/10/2021, 13/12/2021, 29/11/2021, 22/11/2021, 15/11/2021, 08/11/2021, 25/10/2021, 20/12/2021, 09/12/2021, 01/10/2021, 08/10/2021, 18/10/2021, 22/10/2021, 29/10/2021, 05/11/2021, 12/11/2021, 19/11/2021, 26/11/2021, 10/12/2021, 17/12/2021, 03/12/2021

I want to create a formula that converts this list of dates into (just the month of each date as an array):
09,09,09,10,10,10,10,12,11,11,11,11,10,12,12,10 etc…

If I extract the month before producing the full array of dates, of course, it only ever gives me 1 instance of each month which is not what I want.

I’ve been trying to work out the formula that will give me this result, but I don’t have much experience with nested formulas which I think is what is needed here…? Can anyone point me in the right direction?

Many thanks

[Justin_Barrett]

Welcome to the community, @Rosa_Ramos! :grinning_face_with_big_eyes:

Rosa_Ramos:

If I extract the month before producing the full array of dates, of course, it only ever gives me 1 instance of each month which is not what I want.

That was going to be my suggestion, so there’s something about your base structure that’s not what I think it is if you’re only getting a single instance of each month using that method. Could you describe your setup in greater detail? Does each rolled-up date correspond to a single record in the target table, or does each record contain multiple dates (effectively meaning that you have an array of arrays, not just a single-level array)? Also, what’s your end goal with creating this list of months? Do you want to process that list further?

The more details you can share about your use case, the easier it will be to help.

[kuovonne · Accepted solution]

It sounds like you want unique dates, but not unique months. Thus, extracting the month and then rolling up the months will not work–either you will have unique months, or you will have a month for every date (not unique dates).

You can use a regular expression formula on the result that you are showing in your post:

REGEX_REPLACE({Rollup of dates}, "(\\d{2}/)(\\d{2})(/\\d{4})", "$2")

I have not encountered someone needing data in this format before, especially since the months will not be sorted. I’m a little curious. Care to share why you need the data formatted this way?

[Rosa_Ramos]

Hi @Justin_Barrett, thanks for replying! :slightly_smiling_face:

Exactly - it’s an array of an array and the reason why I’m trying to create the list is because I want to count the number of times a class happened in a month (actually, thinking about it now it should probably be month and year :grinning_face_with_sweat: ) in order to create a (repeating) invoice that will only include the classes for the month when the invoice is produced.

Basically, in my invoice table I want to create a field that says count the number of times the number in the array matches the month number of the current month only and multiply that by the cost of the class. This way, next month, I can duplicate the previous invoice for that company, but with a new invoice date e.g. 01/2022, the only classes that will be counted within that invoice are those that happened in that month.

Let me explain a bit more:
I have a Timesheets table that records the time and date a class happened as well as any students that attended the class.

A Student table that holds student information including all the classes they attended.

A Company Classes table that creates groups of students and holds any information related to that group. The groups are not directly linked to the timesheets (this is likely a mistake, but was done intentionally because I wanted to make it easy for teachers to create their timesheet data and decided that all they needed to do was to select the students in the class on any particular day, rather than choose the class name and then select the students as well).

The Invoice table references the Company classes table and I have been doing month by month calculations to work out how many classes took place for a group by pulling up all the classes that every student in that class has done in the current month, flattening the arrays and then eliminating duplicates.

I want to find a way to do this month by month process automatically.

[Rosa_Ramos]

Hi @kuovonne, than you for replying to my query :grinning_face_with_big_eyes:

I’ve explained why I’d like to create the month list this way in my reply to Justin above, I hope you don’t mind that I’m not explaining it again here - it’s a bit convoluted! :laughing:

Thanks for writing out the formula for me. I have tried using it, but it’s not working:

What am I doing wrong?

[Rosa_Ramos]

Ooh! Actually, I read a little about Regex functions and changed the rollup field to a formula field with an arrayjoin function and it’s has given me the results I wanted. Thank you so much. :star_struck: