Sample Date Formulas

As you work with the example formulas in this topic, keep these considerations in mind.

• To avoid syntax errors when copying formulas, replace:

  • date placeholders with a real DATE value, such as a Date field like CloseDate or the TODAY() function.

  • date_time placeholders with a real Date/Time value, such as CreatedDate or NOW().

  • date_1, date_2, earlier_date, later_date, start_date, end_date, start_date_time, end_date_time, num_years, and target_date placeholders with your own fields or values, as appropriate.

• Functions such as DAY(), MONTH(), and YEAR() require Date input. If you’re starting with a Date/Time value, first convert it to a Date with the DATEVALUE() function. For example, DAY( DATEVALUE( date_time )).

Find Out If a Year Is a Leap Year

This formula determines whether a year is a leap year (that is, it has 366 days). This logic is critical for any calculation requiring daily precision, from calculating daily financial interest to tracking medicine dosages. For example, dividing an annual amount by 365 during a leap year results in inaccurate daily data. With this formula, you can dynamically set the calculation denominator to 365 or 366 depending on the year.

OR( 
    MOD( YEAR( date ), 400 ) = 0, 
    AND( 
        MOD( YEAR( date ), 4 ) = 0,
            MOD( YEAR( date ), 100 ) != 0
  )
)

How it works:

• A year is a leap year when it meets one of two specific conditions. One condition is that the year is perfectly divisible by 400 (for example, the year 2000). The other is it’s divisible by 4 but not by 100 (for example, 2024 is a leap year, but 1900 wasn’t).
• The MOD() function calculates the remainder of a division. If MOD( Year, 400 ) = 0, it means that the year is divisible by 400 with no remainder.
• The OR() function wraps the entire formula. If either of the two conditions is true, the formula returns TRUE.

Find Which Quarter a Date Is In

Determining which fiscal quarter a specific activity falls into is essential for activities such as sales forecasting, financial reporting, or performance analysis. Use formulas to calculate the quarter (1–4) for standard years, adjusted fiscal years, or even to compare a date against the current quarter.

Standard Fiscal Year: Use this formula if Q1 begins in January.

CEILING( MONTH ( date ) / 3 )

Shifted Fiscal Year: Use this formula if your fiscal year starts in a month other than January. Adjust the –1 value in ADDMONTHS() to match the number of months necessary to shift back to align with January. For example, the formula as shown shifts back 1 month for a February start.

CEILING( MONTH ( ADDMONTHS ( date, -1 ) ) / 3)

Check if the Date is in the Current (Standard) Quarter: Use this formula to return TRUE if a date falls in the same standard quarter and year as today. This option is useful for situations such as validating current quarter deals or preventing edits to past records.

AND(
    CEILING( MONTH( date ) / 3 ) = CEILING( MONTH( TODAY() ) / 3 ),
    YEAR( date ) = YEAR( TODAY() )
)

How it works:

• The logic in these formulas divides the month number (1–12) by 3 (the number of months in a quarter).

• The CEILING() function rounds the result up to the nearest whole number. For example: February (Month 2) divided by 3 is 0.66, which gets rounded up to 1 (Q1). April (Month 4) divided by 3 is 1.33, which rounds up to 2 (Q2).

• For shifted fiscal years, ADDMONTHS() virtually shifts the date back so the calculation aligns with a January start. For example, by subtracting 1 month, February becomes January (the start of the cycle) for the sake of the formula.

• When checking if a date is in the current quarter, the formula verifies that both the quarter and the year match TODAY() to make sure it doesn’t incorrectly flag dates from Q1 of previous years as current.

Find the Week of the Year a Date Is In

Grouping records by week is a fundamental requirement for activities such as weekly sales reporting, supply chain planning, or resource use tracking. Because different organizations define Week 1 differently, Salesforce provides two methods: the ISO Standard (best for business reporting) and the Absolute Count (best for simple, localized annual progress tracking).

ISO Standard Week (recommended): Use this formula to align most business reporting with ISO-8601 standards. Weeks always begin on Monday, and the first week of the year that contains a Thursday counts as Week 1.

ISOWEEK( date )

Simple Absolute Week Count: Use this formula if your organization defines Week 1 as always starting on January 1, regardless of the day of the week. This approach is useful for simple annual counters where you simply want to know how many 7-day blocks have passed since the year began.

CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7)

How it works:

• ISO Standard Logic: The ISOWEEK() function is a native tool that automatically calculates the week number (1–53) based on ISO-8601 definitions. It automatically handles edge cases where January starts in the previous year's final week, or December ends in the next year's first week.
• Absolute Count Logic:
  • The formula date - DATE( YEAR( date ), 1, 1) + 1 calculates the “day number” of the year. For example, February 1 is the 32nd day. Then it divides the day count by 7 to find how many weeks have passed. Continuing the example: 32/7 = 4.57.
  • CEILING() rounds the result up to the nearest whole number. In this case, 4.57 rounds up to 5, placing February 1 in Week 5.
  • Because 365 days doesn’t divide evenly by 7 (365 days ÷ 7 = 52.14), the last 1 or 2 days of the year fall into a partial Week 53.

Find Whether Two Dates Are in the Same Month

For activities such as commission calculations, campaign attribution, or consolidated billing, it’s often necessary to determine if two events occurred in the same accounting or reporting period. This formula checks if two specific date fields (for example, an Opportunity CloseDate and a custom Invoice_Date__c) fall within the same calendar month.

AND(
    MONTH( date_1 ) = MONTH( date_2 ),
    YEAR( date_1 ) = YEAR( date_2 )
)

You can also dynamically apply this logic to validate whether a date falls within the current month relative to today. This approach is useful for conditional formatting, like highlighting tasks due this month, or creating formula flags for "This Month" reporting that updates automatically.

AND(
    MONTH( date ) = MONTH( TODAY() ),
    YEAR( date ) = YEAR( TODAY() )
)

How it works:

• The formula uses MONTH() to extract the number (1–12) for both dates and compares them.

• It also uses YEAR() to ensure the years match. Without this step, a date in January 2024 incorrectly matches a date in January 2023.

• The AND() function requires both the month and the year to be identical to return TRUE.

Find the Last Day of the Month

Calculating the last day of the month is essential for financial and contract management use cases. Common uses cases include setting dynamic subscription end dates, determining invoice due dates, or reporting on deals closing at the month's end. This formula simplifies handling 28–, 30–, and 31–day months, and leap years, by subtracting one day from the first day of the next month.

ADDMONTHS( DATE( YEAR( date ), MONTH( date ), 1 ), 1 ) - 1

How it works:

• The formula DATE( YEAR( date ), MONTH( date ), 1 ) creates a date that’s set to the 1st day of the month from your original date field.
• ADDMONTHS( ..., 1 ) moves that "1st of the month" date forward exactly one month. For example, changing January 1 to February 1.
• Subtracting 1 from the first day of the next month automatically reverses the date to the last valid day of the current month. This conversion automatically handles February 28/29 and 30/31 day months correctly.

Show the Month as a String instead of a Number

While standard date fields appear numerically, such as 01/15/2024, reporting and customer-facing communications often require a more polished format. This formula converts the numeric month value into its full-text name, such as January. This conversion is useful for generating professional headers (for example, “January 15” rather than “1/15”) in resources such as quote documents, email templates, or marketing newsletters.

CASE(
    MONTH( date ),
    1, "January",
    2, "February",
    3, "March",
    4, "April",
    5, "May",
    6, "June",
    7, "July",
    8, "August",
    9, "September",
    10, "October",
    11, "November",
    12, "December",
    ""
)

If your organization uses multiple languages, replace the month names with custom labels that you can translate. With this approach, the month name dynamically updates based on the user's language settings. For example, it shows "January" for US users and "Janvier" for French users.

CASE(
    MONTH( date ),
    1, $Label.Month_of_Year_1,
    2, $Label.Month_of_Year_2,
    3, $Label.Month_of_Year_3,
    4, $Label.Month_of_Year_4,
    5, $Label.Month_of_Year_5,
    6, $Label.Month_of_Year_6,
    7, $Label.Month_of_Year_7,
    8, $Label.Month_of_Year_8,
    9, $Label.Month_of_Year_9,
    10, $Label.Month_of_Year_10,
    11, $Label.Month_of_Year_11, 
    12, $Label.Month_of_Year_12,
    ""
)

How it works:

• The formula uses the MONTH() function to extract the numerical month (1–12) from the date.
• The CASE() function then compares that number against the defined list. If the month is 1, it returns the first value ("January" or $Label.Month_of_Year_1); if 2, it returns the second value, and so forth.
• The final line ("") provides a default value. If the date field is blank, the CASE() statement falls through to this line and returns an empty text string, ensuring no error occurs.

Find and Show the Day of the Week from a Date

While Salesforce stores dates numerically (such as 2024-12-25), external communications and internal schedules often require a more human-readable format. This formula identifies the specific day of the week from a date field and converts it into text (for example, "Wednesday"). This function is useful for email marketing templates where you want to personalize the greeting with something like "Happy Friday!" Another example use case is staffing reports, so you can easily spot and filter for weekend activity.

CASE(
    WEEKDAY ( date ),
    1, "Sunday",
    2, "Monday",
    3, "Tuesday",
    4, "Wednesday",
    5, "Thursday",
    6, "Friday", 
    7, "Saturday",
    ""
)

If your organization uses multiple languages, replace the name of the day with custom labels that you can translate. With this approach, the day name can change dynamically based on the user's language settings (such as showing "Domingo" for Spanish users).

CASE(
    WEEKDAY( date ),
    1, $Label.Day_of_Week_1,
    2, $Label.Day_of_Week_2,
    3, $Label.Day_of_Week_3,
    4, $Label.Day_of_Week_4,
    5, $Label.Day_of_Week_5,
    6, $Label.Day_of_Week_6,
    7, $Label.Day_of_Week_7,
    ""
)

How it works:

• The WEEKDAY( date ) function examines the date and returns a number from 1 (Sunday) to 7 (Saturday).
• The CASE() function compares that number against the list of values. If it finds a 1, it returns the first value ("Sunday" or $Label.Day_of_Week_1). If it finds a 2, it returns the second value, and so forth.
• The final line ("") provides a default value. If the date field is blank, the CASE() statement falls through to this line and returns an empty text string instead of an error.

Find the Next Day of the Week After a Date

Calculating the specific date of the "next Monday" or "upcoming Friday" is important for setting consistent weekly schedules. For example, perhaps your support team performs quality audits every Friday, or billing cycles start on a Monday. This formula automatically calculates the correct upcoming date regardless of the record’s creation date.

date + ( target_day - WEEKDAY( date ) +

IF( target_day <= WEEKDAY( date ), 7, 0 ) )

Replace target_day with the number corresponding to the day that you want: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.

How it works:

• The formula calculates the gap between the target_day (the day you want) and the WEEKDAY( date ) (the current day of the record).
• The IF() statement determines if the target day is later this week or if it wraps around to the next week.
  • Later this week: If today is Monday (2) and you want Friday (6), the math is simply 6 - 2 = 4 days away. The IF adds 0.
  • Past or today: If today is Friday (6) and you want Monday (2), the math is 2 – 6 = –4 (meaning Monday was 4 days ago). Because the target is "less than or equal to" today, the IF statement adds 7 to the result: –4 + 7 = 3 days away (the next Monday).

Find the Number of Days Between Two Dates

To measure efficiency, such as tracking Sales Cycle time (Close Date minus Created Date) or Case Resolution time, calculate the duration between two specific events. These formulas calculate the precise number of elapsed days or automatically flag records that exceed a specific time threshold.

Basic Duration Calculation: To find the simple number of days between two specific dates, subtract the earlier date from the later date.

later_date — earlier_date

Check if a Date Occurred More Than 30 Days Ago: You can also use date math to flag records that have become stale or overdue. For example, use this formula to identify leads with no activity in a month or invoices that are past due. It returns TRUE if the date is more than 30 days in the past relative to today.

( TODAY() - date ) > 30

How it works:

• Salesforce stores dates internally as simple numbers (integers). When you subtract one date from another (for example, later_date - earlier_date), Salesforce calculates the simple difference between those integers, returning the result as a whole number of days.
• In the second formula, TODAY() - date subtracts the field’s date from the current system date to calculate the age of the record in days.
• The > 30 operator compares that age against your threshold. If the difference is 31 days or higher, the formula returns TRUE. Otherwise, it returns FALSE.

Find the Number of Weekdays Between Two Dates

For activities such as tracking the number of working days to resolve a support case or close a sales opportunity, calculate the number of business days (weekdays) between two dates. Unlike simple date subtraction, which counts weekends, this formula ensures you only count Monday through Friday.

(
    5 * FLOOR( ( end_date - DATE( 1900, 1, 8 ) ) / 7 ) +
    MIN( 5, MOD( end_date - DATE( 1900, 1, 8 ), 7 ) )
)
-
(
    5 * FLOOR( ( start_date - DATE( 1900, 1, 8 ) ) / 7 ) +
    MIN( 5, MOD( start_date - DATE( 1900, 1, 8 ), 7 ) )
)

Ensure end_date is the later date and start_date is the earlier date to get a positive number.

How it works:

• The formula calculates a weekday score for end_date and start_date, then subtracts the start_date score from the end_date score to get the number of weekdays between them.
• The formula relies on a fixed reference date: Monday, January 8, 1900. It uses a known Monday to serve as a Day 0 anchor.
• FLOOR( ( end_date - ... ) / 7 ) and FLOOR( ( start_date - ... ) / 7 ) calculate how many full 7-day weeks have passed since the reference date. The formula multiplies this count by 5 to credit five business days for every full week.
• The MOD() function looks at the remaining days (0–6) after the full weeks are counted. The MIN( 5, ... ) function caps the partial-week weekday count at 5 so Saturday and Sunday don’t increase the weekday total past Friday.

Note This formula works for a standard 5-day work week (Monday through Friday). Adapting it for other schedules, such as a 6-day week or shifts that include weekends, requires adjusting the reference date and the math logic, which is an advanced customization not covered in this topic.

Find the Number of Months Between Two Dates

Activities like subscription management or contract renewals often require calculating the number of calendar months between two dates. For example, maybe you’re determining the duration of a multi-year agreement for billing or calculating employee tenure for benefits. This formula finds the difference based on month and year values, ignoring the specific day of the month.

( ( YEAR( later_date ) - YEAR( earlier_date ) ) * 12 ) + (MONTH ( later_date ) - MONTH( earlier_date ) )

How it works:

• ( YEAR( later_date ) - YEAR( earlier_date ) ) * 12 calculates the difference in years between the two dates and multiplies that by 12 to convert it into a baseline number of months.
• MONTH( later_date ) - MONTH( earlier_date ) calculates the difference between the month numbers (1–12).
• Finally, the formula adds these two values together.

Note This calculation strictly considers the calendar month. For example, a start date of January 31 and an end date of February 1 returns 1 month, because the month integer has increased by one, even though only one day has passed.

Add Days, Months, and Years to a Date

Calculating future dates is an important requirement for workflow automation. These formulas dynamically project dates into the future. For example, use them for activities such as setting a follow-up task deadline for 5 days after a call, determining a contract expiration date, or calculating an employee's vesting schedule.

Add Days: To schedule short-term follow-ups or deadlines, add the number of days directly to the date field. Salesforce treats dates as integers, so adding 5 increases the date by 5 days.

date + 5

Add Months: For medium-term schedules like quarterly reviews or subscription renewals, use ADDMONTHS(). This function is smarter than using simple addition because it automatically handles months of different lengths (28, 30, 31 days).

ADDMONTHS( date, 1 )

Note If you add 1 month to January 31, the result isn’t February 31 (which doesn’t exist). The function automatically snaps to the last valid day of the month—in this case, February 28 (or 29 in a leap year).

Add Years: Use this formula for calculations like determining multi-year agreement durations for billing or calculating employee tenure for benefits. By multiplying the number of years by 12, you can use the ADDMONTHS() logic to ensure accurate year-over-year calculations.

ADDMONTHS( date, 12 * num_years )

Replace num_years with the number of years that you want to add.

Leap Years: By default, if you add years to a leap year (February 29) and the future year isn’t a leap year, Salesforce returns February 28. If you have a business requirement that the anniversary of February 29 is March 1 instead, which is common in some legal contracts, use this advanced formula.

ADDMONTHS( date, 12 * num_years ) +
IF(
    AND(
        DAY( date ) = 29,
        DAY( ADDMONTHS( date, 12 * num_years ) ) = 28
    ),
    1,
    0
)

This logic checks if the date was adjusted to the 28th and adds one day to push it to March 1.

How it works:

• The date + 5 formula simply increases the integer value of the date.
• The ADDMONTHS( date, ... ) formula adds calendar months. Multiplying years by 12 (12 * num_years) converts years into months so the function can process them.
• In the leap year formula, the IF() statement checks two conditions: that the original day was the 29th and the calculated future day is the 28th. If both are true, it indicates the system adjusted for a non-leap year. The formula then adds 1 day to push the date forward to March 1.

Add Business Days to a Date

Calculating deadlines based on working days is critical for activities such as maintaining service-level agreements (SLAs) or managing customer expectations. For example, if support promises a 3-business-day response, simply adding 3 calendar days to a Friday request incorrectly sets a Sunday deadline (a non-working day). This formula automatically adjusts the due date to skip weekends, making sure that a request always lands correctly on a weekday.

This example adds 3 business days.

CASE( 
    WEEKDAY( date ),
    4, date + 5, 
    5, date + 5, 
    6, date + 5, 
    7, date + 4, 
    date + 3
)

To add a different number of days, adjust the logic inside the CASE() statement. For example, here’s an adjusted formula for 4 business days.

CASE(
    WEEKDAY( date ),
    1, date + 4,  /* Sunday -> Thursday */
    2, date + 4,  /* Monday -> Friday */
    3, date + 6,  /* Tuesday -> next Monday */
    4, date + 6,  /* Wednesday -> next Tuesday */
    5, date + 6,  /* Thursday -> next Wednesday */
    6, date + 6,  /* Friday -> next Thursday */
    7, date + 5   /* Saturday -> next Thursday */
)

How it works:

• These formulas assume a standard Monday–Friday work week. They don’t account for specific holidays. The approach in the formulas treats the start date as Day 0 and counts future business days only.
• The CASE() function checks the day of the week of your start date (WEEKDAY( date )) and adds a specific number of calendar days to ensure the result lands on a valid business day.
• Here’s how the 3-business-day formula handles adjustments.
  • For Wednesday (4), Thursday (5), Friday (6): Adding 3 business days forces the deadline to a weekend date. The formula adds 5 calendar days (3 business days + 2 weekend days) to bridge the gap.
  • For Saturday (7): If the date starts on a Saturday, the formula adds 4 calendar days (Sunday + Monday/Tuesday/Wednesday) to land on Wednesday.
  • For Sunday (1), Monday (2), Tuesday (3): For these days, adding 3 business days doesn’t cross a weekend. The formula acts as the default case and simply adds 3 calendar days.

Find the Hour, Minute, or Second from a Date/Time

Extracting specific time components from a Date/Time field is important for granular reporting and scheduling. Some examples include flagging support cases created after business hours or analyzing call volume patterns by hour of the day to optimize staffing. These formulas isolate the exact hour, minute, or second from a timestamp.

Important Salesforce stores all Date/Time values in GMT (Greenwich Mean Time). To get accurate local times, replace a formula’s TZoffset placeholder with the specific numeric calculation for your time zone difference.

• If you’re behind GMT, replace TZoffset with the number of hours you’re behind divided by 24. For example, for Eastern Time (UTC-5), use the value of 5.0 / 24.

• If you’re ahead of GMT, change the minus sign (–) in the formulas with a plus sign (+). And then replace TZoffset with the number of hours that you’re ahead divided by 24. For example, for Singapore (UTC+8), use the value of 8.0 / 24.

For more information, see the note about Date/Time and time zones in Using Date, Date/Time, and Time Values in Formulas.

Find the Hour: Use these formulas to extract the hour as a number. This approach is the primary method for "Time of Day" analytics.

• 24-Hour Format (0–23): Best for calculations and sorting.

  HOUR( TIMEVALUE( date_time - TZoffset ) )

• 12-Hour Format (1–12): Best for user-facing interactions.

  IF( 
      OR( HOUR( TIMEVALUE( date_time - TZoffset ) ) = 0, HOUR( TIMEVALUE( date_time - TZoffset ) ) = 12 ),
      12,
      MOD( HOUR( TIMEVALUE( date_time - TZoffset ) ), 12 )
  )
  

Find the Minute and Second: Use these formulas to extract minutes or seconds (0–59). This approach is useful for calculating precise durations, such as Time to First Response.

• Minutes:

  MINUTE( TIMEVALUE( date_time - TZoffset ) )

• Seconds:

  SECOND( TIMEVALUE( date_time - TZoffset ) )

Determine AM or PM: Use this formula to return AM or PM as a text string. This output is useful when building custom 12-hour timestamps for resources, such as email templates or reports.

IF( 
    HOUR( TIMEVALUE( date_time - TZoffset ) ) < 12, 
    "AM", 
    "PM" 
)

Show Full Time as a String (HH:MM:SS AM/PM): When you want to show the time in a polished, human-readable format such as 09:05:30 AM, use this composite formula. It combines the logic from the other formulas in this section, and uses LPAD() to ensure the hour, minute, and second values always appear as two digits (for example, 09 instead of 9).

LPAD( TEXT( IF(
    OR(
        HOUR( TIMEVALUE( date_time - TZoffset ) ) = 0,
        HOUR( TIMEVALUE( date_time - TZoffset ) ) = 12
    ),
    12,
    MOD( HOUR( TIMEVALUE( date_time - TZoffset ) ), 12 )
) ), 2, "0" )
& ":" &
LPAD( TEXT( MINUTE( TIMEVALUE( date_time - TZoffset ) ) ), 2, "0" )
& ":" &
LPAD( TEXT( SECOND( TIMEVALUE( date_time - TZoffset ) ) ), 2, "0" )
& " " &
IF( HOUR( TIMEVALUE( date_time - TZoffset ) ) < 12, "AM", "PM" )

How it works:

• The TIMEVALUE() function extracts the time portion (HH:MM:SS) from the Date/Time field.
• Subtracting the offset (for example, - 5.0/24) shifts the GMT time to your local time before the extraction happens.
• 12-Hour Logic:
  • The MOD(..., 12) function calculates the remainder when dividing the hour by 12 (for example, 14 becomes 2).
  • The IF statement handles the edge cases for Noon (12 PM) and Midnight (0 AM), representing them as 12 instead of 0.
• LPAD(..., 2, "0") pads single digits with a leading zero, ensuring times look standard (for example, 09:05 instead of 9:5).

Find the Elapsed Time Between Date/Times

Measuring the precise duration of a workflow is necessary for performance tracking. For example, determining exactly how long a support case remains open (down to the minute) helps enforce service-level agreements (SLAs). Another example is highlighting efficiency gaps by tracking the time lag between a lead's creation and the first sales activity. These formulas calculate this duration as a simple number or a human-readable text string.

Simple Day Count: If you’re calculating the difference in full days, simply subtract the earlier date from the later date. This approach returns a number. For example, 1.5 represents 1 day and 12 hours).

later_date  -  earlier_date

Formatted Elapsed Time String (Days, Hours, Minutes): To show the duration in a readable format like "2 days 4 hours 15 minutes," use this formula. It handles the conversion of the decimal date difference into specific units of time.

IF(
    end_date_time - start_date_time > 0 ,
    TEXT( FLOOR( end_date_time - start_date_time ) ) & " days "
    & TEXT( FLOOR( MOD( (end_date_time - start_date_time ) * 24, 24 ) ) ) & " hours "
    & TEXT( FLOOR( MOD( (end_date_time - start_date_time ) * 24 * 60, 60 ) ) ) & " minutes",
    ""
)

How it works:

• The IF statement makes sure that end_date_time is actually after start_date_time. If the result is negative because of a user error or bad data, the formula returns a blank value instead of a confusing negative string.
• FLOOR( end_date_time - start_date_time ) takes the total difference and isolates the integer (whole number) to count full days.
• MOD( (diff) * 24, 24 ) takes the remaining decimal (the partial day), multiplies it by 24 to convert it to hours, and isolates the hours that don't make up a full day.
• MOD( (diff) * 24 * 60, 60 ) multiplies the difference by 1440 (the number of minutes in a day) to get the total minutes, then uses MOD to find the remaining minutes after removing the full hours. the hours.
• The final FLOOR() keeps the minutes value as a whole number and prevents the minutes component from rounding up to 60.

Find the Number of Business Hours Between Two Date/Times

Accurately tracking service-level agreements (SLAs) often requires measuring duration in "business hours" rather than raw clock time. For example, consider a high-priority support case that arrives at 4:00 PM on Friday and gets resolved at 10:00 AM on Monday. The actual elapsed time is 66 hours, but assuming a 9 to 5 schedule, the business duration is only 2 hours. This formula calculates that specific business duration, automatically excluding weekends and non-working evening hours.

Note The formula assumes an 8-hour workday (9:00 AM to 5:00 PM) and that the user works in the UTC-8 (Pacific Standard Time) zone. It also assumes a fixed offset and doesn’t account for Daylight Saving Time (DST) changes, specific holidays, or your organization’s configured business hours.

ROUND( 8 * (
    ( 5 * FLOOR( ( end_date_time -
        DATETIMEVALUE( "1900-01-08 17:00:00") ) / 7) +
    MIN( 5,
        FLOOR( MOD( end_date_time -
            DATETIMEVALUE( "1900-01-08 17:00:00"), 7) / 1) +
        MIN( 1, 24 / 8 * ( MOD( end_date_time -
            DATETIMEVALUE( "1900-01-08 17:00:00" ), 1 ) ) )
    )
    )
    -
    ( 5 * FLOOR( ( start_date_time -
        DATETIMEVALUE( "1900-01-08 17:00:00") ) / 7) +
    MIN( 5,
        FLOOR( MOD( start_date_time -
            DATETIMEVALUE( "1900-01-08 17:00:00"), 7) / 1) +
        MIN( 1, 24 / 8 * ( MOD( start_date_time -
            DATETIMEVALUE( "1900-01-08 17:00:00" ), 1) ) )
    )
    )
), 2 )

How it works:

• The formula measures the working time that has passed since a fixed historical anchor to calculate a Business Hour Score for the start and end dates. It then subtracts the start score from the end score to find the difference.
• The formula relies on a fixed reference date of 1900-01-08 because it was a Monday. This reference allows clean calculations for 5-day work weeks.
• The time 17:00:00 aligns with the start of the workday. This time corresponds to 5:00 PM GMT, which is the equivalent of a 9:00 AM start time in the Pacific time zone (UTC-8).
  • To change the start time or time zone, calculate your start time in GMT by adding your time zone offset to your local start time. For example, if you’re in New York (UTC-5) and start at 9:00 AM, the math is 9 + 5 = 14. Replace 17:00:00 in the formula with 14:00:00. See the note about Date/Time and time zones in Using Date, Date/Time, and Time Values in Formulas for more information.
• The formula defines the workday length as 8 hours. The 24 / 8 fraction scales the partial day to match the business day definition.
  • If you have a different shift length, such as 9 hours, adjust the formula by changing the three occurrences of 8 to the appropriate number (for example, 9). It’s important that you don’t change the 08 in the date strings as that number refers to the day of the month.
• The MIN( 5, ... ) logic ignores Saturday and Sunday. If the score exceeds 5 days (Friday), it caps the value, effectively pausing the clock until Monday.