Calculate utilization days in a given month from a start date and end date (Or timeline)

How To
, ,
1

Hello there! Looking for help with the following scenario that I can’t manage to even get a good start on how to solve it. I’m trying to calculate usage of a work vessel for a given month within a timeline. Example:

Four columns on a Monday.com board: Timeline, Duration,June Days and July Days.

Capture
Capture708×446 12.3 KB

In Make, you can grab the start date and end date from the timeline, so that’s no big deal. What I want to happen is when the user adjusts the timeline in Monday, Make will run a scenario that puts the number of days in June that are included in the June Days column and the number of days in July that are included in the July days column. I can’t come up with a way to solve this one. Any ideas?

2

Hello Brian,

Here is how I might approach this, it’s complicated so I’m hoping someone else has a less complicated method…

  1. Use a repeater (starting at 0, ending at 364) combined with a JSON aggregator to create a collection of date objects starting from your starting date
    Use a filter to stop adding items when the date exceeds the range end date.
    Each object should contain data points like date, month, day, year, day of week, name of month, etc… whatever you need to help filter, which is this name is name of Month.
  2. Parse the JSON
  3. Aggregate JSON into a single array
  4. Iterate on the list of Name of Month in the array.
  5. Use map to query the array, create an array of dates for each month, counting the days in each month.

Here’s what I started with:
image

And result:
image

The end is one Operation for each month included in the range, which in this case is 2, June and July. In your scenario you wouldn’t need this, it’s just for illustration.

I’ll post this blueprint, but you’ll need to build your own JSON Data Structure for it to work correctly. Here’s sample data to use for the Data Structure:

  {
        "Day": 21,
        "Date": "2024-06-21T15:06:00.000Z",
        "Year": 2024,
        "Month": 6,
        "Month Name": "June",
        "Is Weekend?": false,
        "Day of Week Name": "Friday"
    }

Blueprint: JuneJulyDays.json (14.1 KB)

1 Like
3

Hey @Brian_LaRose

Please find the attached looms. I believe these will help you.

  1. Watch for Timeline Change
    Calculate utilization days in a given month from a start date and end date (Or timeline) - Questions & Answers - Make Community - 24 June 2024 | Loom

  2. Update the Timeline
    Community Testing Scenario | Make - 24 June 2024 | Loom

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support | Book Live Implementation
Visit us here
Youtube Channel

1 Like
4

Thank both of you for your input! I ended up solving this with a formula column natively in Monday, with the help of one of the formula gurus at Monday. Here is the formula I used (just changed the dates for each month).

IF(IF(AND(FORMAT_DATE({Timeline#Start}, “YYYY-MM”)<=“2024-06”,FORMAT_DATE({Timeline#End}, “YYYY-MM”)>=“2024-06”), “yes”, 0)=“yes”,(DAYS(IF({Timeline#End} > “2024-06-30”, “2024-06-30”, {Timeline#End}),IF({Timeline#Start}< “2024-06-01”, “2024-06-01”, {Timeline#Start}))+1),0)

2 Likes