Extract dates/days from DateRange

Get Help
1

Background

I have a Date Range field that users can use to request vacation. The date range can span months but for the sake of simplicity let’s say we can have a Date Range that spans a maximum of two months (e.g. the red box)

image
image477×780 54.6 KB

Problem

I’m trying to extract the number of days in each month of the Date Range. E.g. in the example below, August has 2 days and September has 6 days. I can get the total number of days with a formula but not split up between the months.

Desired result

A way to keep track of how many days are in each month of the Request field.

2

I’m doing something similar in our workspace (for salary computation) and I hadn’t found a way around doing the full formula of:

  1. Check if start and end is in the same month
  2. If no, manually count the days in the start month and the days in the end month
  3. Add all the months in between

That said, overall, what would you want the outcome to be? Does one Leave have an array of August: 2, September 6?

The alternative is using data modelling, where a leave request generates Off-Days for every day between them through a JS script and then you have a database where you can query and tally however you want.

3

Yeah basically an array like you described.

I didn’t think of using automations to generate dates from the request, going to try that. Thanks for the idea!

4

Fibery does not have the concept of an array data type, so as @njyo points out, it’s not clear how you would store/represent the result.
FWIW, what you are trying to achieve id probably possible in Report view, but maybe you need the data in the database itself.

5

Even if you concatenate that array into a text field, that’ll be a monster formula and parsing it later would be a pain.

I think modelling the entities will have a more manageable result. Will still be complex enough. :face_with_peeking_eye:

6

I stuck this in a formula field and it seems to give me days in the first month, you could do a second field for the second month.

If(
  Month(Dates.Start()) != Month(Dates.End()),
  ToDays(
    Date(Year(Dates.Start()), Month(Dates.Start()) + 1, 1) -
      Date(Year(Dates.Start()), Month(Dates.Start()), Day(Dates.Start()))
  ),
  0
)
7

Sure, you can do it by having two formula fields, but then you are definitely limited to date ranges that span no more than two months.
Consider also, if a date range does not span two months, which formula should give the number of days?

8

I got it working by using an automation.

I have added a relation field called Dates (Many to Many) with the Dates space @ChrisG added a long time ago (can’t find reference to it now, will update post if someone finds it).

image
image863×1042 66.1 KB

It can be filled out manually of course but also automated with this process.

image
image1314×1029 69.9 KB

Full script (could be cleaned up but works for now)

// Fibery API is used to retrieve and update entities
const fibery = context.getService('fibery');

// affected entities are stored in args.currentEntities;
// to support batch actions they always come in an array
for (const entity of args.currentEntities) {
    // Get dates from request
    const requestDates = getDates(entity.Request.start, entity.Request.end);

    // Get date entities from the Dates database
    const requestDatesString = '[\"' + requestDates.join('\",\"') + '\"]';
    const graphqlQuery = "{findDates(name: { in: " + requestDatesString + " }) {itemId: id}}";
    const dateEntitiesResults = await fibery.graphql('Dates', graphqlQuery);
    const dateEntities = dateEntitiesResults.data.findDates;

    // Add current entity id to each object
    dateEntities.forEach(dateEntity => {
        dateEntity.id = entity.id;
    });

    // Link Dates with Vacation
    await fibery.addCollectionItemBatch(entity.type, 'Dates', dateEntities);
}

/**
 * Helper function to get dates in the format yyyy-mm-dd
 */
function getDates(start, end) {
    for (var arr = [], dt = new Date(start); dt < new Date(end); dt.setDate(dt.getDate() + 1)) {
        let tempDate = new Date(dt);
        arr.push(tempDate.toISOString().split('T')[0]);
    }
    return arr;
};

From here on it’s fairly simple to count dates in each month (I think, haven’t gotten to that part yet :smiley:)

I :heart: Fibery - it’s so powerful when you can script almost anything!

1 Like
9

If you’re going to use a prefilled Date database, I reckon you could do this without scripting, just by using an automation formula, something like

Update Dates field:
Dates.filter(Date >= [Step 1 Vacation].[Request Start] and Date <= [Step 1 Vacation].[Request End])

1 Like
10

Very nice, just tried it and it works! Much simpler as well :slight_smile: