Days in Quarter field

[CClark3]

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. 

[RSchweighart · Accepted solution]

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.

 

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]

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