CRM

CClark3
Member

Days in Quarter field

SOLVE

Hello,

 

Its me with another formula field question, I need to create properties that calculate the number of days a deal is in each quarter. So our projects can last anywhere from 1 month to a year or more, so we would like to calculate how many days a project spends in each quarter. For example if a project starts on Feb.1 and ends June 1 how amny days in Q1 did we receive revenue and in Q2. I was succesfull at creating a formula that captures the days for Q1 2024 but I'm running into problems when trying to create Q2 and so on. I've attached the screenshot of both the Q1 formula that works and the Q2 formula that does not bring the correct results. I need to create these fields for all 4 quarters for 2024, 2025,2026. 

Days in Q1.PNGDays in Q2.PNG

0 Upvotes
1 Accepted solution
RSchweighart
Solution
Top Contributor | Gold Partner
Top Contributor | Gold Partner

Days in Quarter field

SOLVE

Hi @CClark3,

 

Here's a formula that should work with the assistance of ChatGPT. I did some testing, and it seems to be working. That said, be sure to extensively test with your data.

 

You'll create a property for each quarter with this general formula:

 

if(
  min([properties.project_end_date], Quarter_End_Milliseconds) - max([properties.project_start_date], Quarter_Start_Milliseconds) >= 0,
  (
    time_between(
      max([properties.project_start_date], Quarter_Start_Milliseconds),
      min([properties.project_end_date], Quarter_End_Milliseconds)
    ) / 86400000
  ) + 1,
  0
)

 

 

This is what it would look like for Q1 2024:

 

IF(
  MIN([properties.project_end_date], 1711843200000) - MAX([properties.project_start_date], 1704067200000) >= 0,
  (
    time_between(
      MAX([properties.project_start_date], 1704067200000),
      MIN([properties.project_end_date], 1711843200000)
    ) / 86400000
  ) + 1,
  0
)

 

 

You may need to modify the + 1 piece of the formula depending on whether to include or exclude the start/end dates in the total days calculation.

 

Use Excel or something to calculate the millisecond start/end times for each quarter if you need more than what's displayed below:

Quarter Start Date End Date Start Milliseconds End Milliseconds
Q4 2023 October 1, 2023 December 31, 2023 1696118400000 1703980800000
Q1 2024 January 1, 2024 March 31, 2024 1704067200000 1711843200000
Q2 2024 April 1, 2024 June 30, 2024 1711929600000 1719705600000
Q3 2024 July 1, 2024 September 30, 2024 1722384000000 1730150400000
Q4 2024 October 1, 2024 December 31, 2024 1730236800000 1738272000000
Q1 2025 January 1, 2025 March 31, 2025 1738368000000 1746144000000
Q2 2025 April 1, 2025 June 30, 2025 1746230400000 1754006400000
Q3 2025 July 1, 2025 September 30, 2025 1756684800000 1764451200000
Q4 2025 October 1, 2025 December 31, 2025 1764537600000 1772572800000
Q1 2026 January 1, 2026 March 31, 2026 1772668800000 1780444800000
Q2 2026 April 1, 2026 June 30, 2026 1780531200000 1788307200000
Q3 2026 July 1, 2026 September 30, 2026 1790985600000 1798752000000
Q4 2026 October 1, 2026 December 31, 2026 1798838400000 1806873600000

 

Here's the table as a screenshot. I pasted values above so you could copy/paste elsewhere easier.

ChatGPT 2024-10-10 at 2.30.45 PM.png

I think that should get you there, but let me know if you have any questions.

 

All the Best,

Ryan Schweighart

Whole Hart Impact, LLC

whimpact.co

I help businesses use HubSpot and Zapier.

View solution in original post

2 Replies 2
RSchweighart
Solution
Top Contributor | Gold Partner
Top Contributor | Gold Partner

Days in Quarter field

SOLVE

Hi @CClark3,

 

Here's a formula that should work with the assistance of ChatGPT. I did some testing, and it seems to be working. That said, be sure to extensively test with your data.

 

You'll create a property for each quarter with this general formula:

 

if(
  min([properties.project_end_date], Quarter_End_Milliseconds) - max([properties.project_start_date], Quarter_Start_Milliseconds) >= 0,
  (
    time_between(
      max([properties.project_start_date], Quarter_Start_Milliseconds),
      min([properties.project_end_date], Quarter_End_Milliseconds)
    ) / 86400000
  ) + 1,
  0
)

 

 

This is what it would look like for Q1 2024:

 

IF(
  MIN([properties.project_end_date], 1711843200000) - MAX([properties.project_start_date], 1704067200000) >= 0,
  (
    time_between(
      MAX([properties.project_start_date], 1704067200000),
      MIN([properties.project_end_date], 1711843200000)
    ) / 86400000
  ) + 1,
  0
)

 

 

You may need to modify the + 1 piece of the formula depending on whether to include or exclude the start/end dates in the total days calculation.

 

Use Excel or something to calculate the millisecond start/end times for each quarter if you need more than what's displayed below:

Quarter Start Date End Date Start Milliseconds End Milliseconds
Q4 2023 October 1, 2023 December 31, 2023 1696118400000 1703980800000
Q1 2024 January 1, 2024 March 31, 2024 1704067200000 1711843200000
Q2 2024 April 1, 2024 June 30, 2024 1711929600000 1719705600000
Q3 2024 July 1, 2024 September 30, 2024 1722384000000 1730150400000
Q4 2024 October 1, 2024 December 31, 2024 1730236800000 1738272000000
Q1 2025 January 1, 2025 March 31, 2025 1738368000000 1746144000000
Q2 2025 April 1, 2025 June 30, 2025 1746230400000 1754006400000
Q3 2025 July 1, 2025 September 30, 2025 1756684800000 1764451200000
Q4 2025 October 1, 2025 December 31, 2025 1764537600000 1772572800000
Q1 2026 January 1, 2026 March 31, 2026 1772668800000 1780444800000
Q2 2026 April 1, 2026 June 30, 2026 1780531200000 1788307200000
Q3 2026 July 1, 2026 September 30, 2026 1790985600000 1798752000000
Q4 2026 October 1, 2026 December 31, 2026 1798838400000 1806873600000

 

Here's the table as a screenshot. I pasted values above so you could copy/paste elsewhere easier.

ChatGPT 2024-10-10 at 2.30.45 PM.png

I think that should get you there, but let me know if you have any questions.

 

All the Best,

Ryan Schweighart

Whole Hart Impact, LLC

whimpact.co

I help businesses use HubSpot and Zapier.

kennedyp
Community Manager
Community Manager

Days in Quarter field

SOLVE

Hey @CClark3! Thanks for this post. It looks like you have put in a lot of work for this set up. 

Like @Jnix284 mentioned in this thread, we could use AI assistance with this question for some ideas. Check out what Hub Helper Harry, a chatGPT trained on HubSpot, suggests: 

To solve the problem of calculating how many days a project spends in each quarter, let's break it down step by step. Since you're working with HubSpot's custom properties and formulas, the challenge lies in determining which days fall within the boundaries of each quarter.

### Steps for the Calculation:

1. **Define Quarter Boundaries**:
   - **Q1**: January 1 to March 31
   - **Q2**: April 1 to June 30
   - **Q3**: July 1 to September 30
   - **Q4**: October 1 to December 31

2. **Determine the Days in Each Quarter**:
   - For each project, you have a start date and an end date. You want to calculate how many days of the project fall within each quarter. The formula needs to compare the project dates with the quarter boundaries.

### Formula Logic Example:

#### For Q1:
```javascript
IF(AND(StartDate <= DATE(2024,3,31), EndDate >= DATE(2024,1,1)),
   MIN(DAYS(DATE(2024,3,31), StartDate), DAYS(EndDate, DATE(2024,1,1))),
   0)
```
This formula checks if the project overlaps with Q1 and calculates the number of days it falls within that quarter.

#### For Q2:
```javascript
IF(AND(StartDate <= DATE(2024,6,30), EndDate >= DATE(2024,4,1)),
   MIN(DAYS(DATE(2024,6,30), StartDate), DAYS(EndDate, DATE(2024,4,1))),
   0)
```
This formula follows the same structure for Q2, adjusting the boundary dates.

#### Repeat for Q3 and Q4 with appropriate dates.

### Adjusting for Projects that Span Multiple Quarters:
- If the project spans multiple quarters, each formula needs to handle the transition between quarters by limiting the number of days to the quarter boundaries.

By using formulas like these in custom properties, you can calculate the exact number of days a deal (project) spends in each quarter based on its start and end dates.
 

I'd also love to invite some other experts to add their advice. Hey @karstenkoehler, @RSchweighart, @Lucila-Andimol any ideas for @CClark3 here? Thanks! 

 

Best, 
Kennedy


Did you know that the Community is available in other languages?
Join regional conversations by changing your language settings !