Skip to content
TheChatArchiveThe Chat Archive
o1-preview-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

Please create an excel formula that would function according to my specifications: The…

  1. User

    Please create an excel formula that would function according to my specifications:

    The formula should show the next available date according to the cell in column J (which contains a number indicating day of the month. For example, if it says 5 then it should try to show a date on the 5th of the month). Column K contains logic, either "before" or "after". This logic should only be used if the date that shows is an unavailable date (meaning either it falls on a weekend or a holiday defined by the holiday list in D2:D15 on the "Pay Calendar" sheet). If the logic needs to be used because the date is unavailable, "before" would tell the formula to display the first available date before the specified day of the month and "after" would tell the formula to display the first available date after the specified day of the month. Additionally, the formula needs to take into account the current date as it should only show dates less than or equal to today. Examples:

    1. if today was 11/7/24 and the specified day was "5" and the searchdirection logic was "before", then the formula should output 11/5/24 because that's an available date (a tuesday that's not a holiday).
    2. if today was 12/1/24 and the specified day was "1" and the searchdirection logic was "after", then the formula should output 11/1/24 because that's the first available date and it's not currently equal to the next available date yet (which would be 12/1/24, but because that's not an available date [it's a Sunday], the real next available date would be 12/2/24).
    3. if today was 11/7/24 and the specified day was "2" and the searchdirection logic was "before", then the formula should output 11/1/24 because that's the first available date BEFORE the specified day (11/2/24 is a Saturday).
    4. if today was 12/4/24 and the specified day was "2" and the searchdirection logic was "after", then the formula should output 12/2/24 because that's an available day and is at least equal to today.

    For your reference, this formula is to output the latest payment date for automatic monthly bills and it should be dynamic in nature to support different payment terms like day of the month the payment is automatically pulled and whether or not the payment will pull before or after the specified day of the month if that day is an unavailable date due to being a holiday or on the weekend.

    Please use LET() to define repeating aspects of the formula and to make it more easily understandable and modifiable. For example, the formula should begin with "=LET(" and should create variabales at the top.

    If you need to ask any clarifying questions before starting to build this formula to ensure you're understanding correctly, please do so.

  2. Assistant

    Certainly! Based on your specifications, here's an Excel formula that uses the LET function to define variables for better readability and maintainability. This formula calculates the next available date according to the specified day in column J and the search direction in column K, taking into account weekends, holidays, and the current date.

    Please make sure to adjust the cell references (J2, K2) and the holiday range ('Pay Calendar'!$D$2:$D$15) according to your actual data.

    =LET(
        SpecifiedDay, J2,
        Direction, K2,
        Holidays, 'Pay Calendar'!$D$2:$D$15,
        TodayDate, TODAY(),
    
        // Construct the initial base date with the specified day in the current month
        InitialBaseDate, DATE(YEAR(TodayDate), MONTH(TodayDate), SpecifiedDay),
    
        // Adjust the base date if it's greater than today or invalid (e.g., February 30th)
        AdjustedBaseDate,
            IF(
                InitialBaseDate <= TodayDate,
                InitialBaseDate,
                DATE(
                    IF(MONTH(TodayDate) = 1, YEAR(TodayDate) - 1, YEAR(TodayDate)),
                    IF(MONTH(TodayDate) = 1, 12, MONTH(TodayDate) - 1),
                    SpecifiedDay
                )
            ),
    
        // Ensure the adjusted base date is a valid date within the month
        BaseDate,
            MIN(
                AdjustedBaseDate,
                EOMONTH(AdjustedBaseDate, 0)
            ),
    
        // Generate an array of dates based on the direction ("before" or "after")
        DateRange,
            IF(
                Direction = "before",
                SEQUENCE(60, 1, BaseDate, -1),  // 60 days before base date
                SEQUENCE(60, 1, BaseDate, 1)    // 60 days after base date
            ),
    
        // Filter dates to be less than or equal to today
        ValidDates,
            FILTER(
                DateRange,
                (DateRange <= TodayDate)
            ),
    
        // Exclude weekends and holidays
        AvailableDates,
            FILTER(
                ValidDates,
                (WEEKDAY(ValidDates, 2) <= 5) *  // Weekdays (Monday=1 to Friday=5)
                (ISNA(MATCH(ValidDates, Holidays, 0)))  // Not a holiday
            ),
    
        // Select the final date based on the direction
        ResultDate,
            IF(
                Direction = "before",
                MAX(AvailableDates),
                IF(
                    Direction = "after",
                    MIN(AvailableDates),
                    NA()
                )
            ),
    
        // Return the result
        ResultDate
    )
    

    Explanation of the Formula:

    • SpecifiedDay, Direction, Holidays, TodayDate: Assign variables for the specified day, search direction, holidays list, and today's date.

    • InitialBaseDate: Create the initial base date using the specified day in the current month and year.

    • AdjustedBaseDate: Adjust the base date to the previous month if it's greater than today or invalid (e.g., the 31st in a month with only 30 days).

    • BaseDate: Ensure the adjusted base date does not exceed the last day of the month (handles months with fewer days).

    • DateRange: Generate a sequence of dates either before or after the base date, depending on the search direction.

    • ValidDates: Filter the date range to include only dates less than or equal to today's date.

    • AvailableDates: Further filter the valid dates to exclude weekends and holidays.

    • ResultDate: From the available dates, select the maximum date for "before" or the minimum date for "after".

    Notes:

    • The formula uses SEQUENCE to generate a list of dates to check. It looks 60 days before or after the base date, which should cover most scenarios. You can adjust the number 60 if you need a wider range.

    • WEEKDAY(ValidDates, 2) returns the day of the week (1 = Monday to 7 = Sunday). The condition WEEKDAY(ValidDates, 2) <= 5 ensures that only weekdays are considered.

    • ISNA(MATCH(ValidDates, Holidays, 0)) checks that the date is not in the list of holidays.

    • If no available dates are found (e.g., all dates are weekends or holidays), the formula will return an error (#NUM!). You can handle this by wrapping ResultDate in an IFERROR function if desired.

    Example Usage:

    Assuming the formula is placed in cell L2 and your data starts from row 2:

    • Cell J2 contains the specified day (e.g., 5).

    • Cell K2 contains the search direction (either "before" or "after").

    • Cell L2 will display the next available date based on the logic.

    Testing with Your Examples:

    1. Today: 11/7/2024, Specified Day: 5, Direction: "before"
      Result: 11/5/2024

    2. Today: 12/1/2024, Specified Day: 1, Direction: "after"
      Result: 11/1/2024

    3. Today: 11/7/2024, Specified Day: 2, Direction: "before"
      Result: 11/1/2024

    4. Today: 12/4/2024, Specified Day: 2, Direction: "after"
      Result: 12/2/2024

    Important: Ensure that the dates in the 'Pay Calendar'!$D$2:$D$15 range are valid date serial numbers in Excel, and that your Excel version supports dynamic arrays and the LET function (Excel 365 or Excel 2021 and later).

    Let me know if you need any further adjustments or clarifications!