Skip to main content

If error and negative

  • December 7, 2021
  • 1 reply
  • 16 views
R

Hi, this is my first post, and forgive me because I’ve been newly converted to Airtable from notion and excel. I’m trying to create a formula, but my problem is that some cells are returning ERROR or negative numbers, which makes sense because in some cases the roles are ones without a physical start date, or their start date is in a future period. In excel I’d normally overlay this up with an iferror and also nest the future start date, but it does not seem to work in the same way. I’d appreciate any help with this. Thanks!

IF(({Annual Base}/12/WORKDAY_DIFF(‘2021-12-01’,‘2021-12-31’)*WORKDAY_DIFF({Physical Start Date},‘2021-12-31’))>{Annual Base}/12,{Annual Base}/12,{Annual Base}/12/WORKDAY_DIFF(‘2021-12-01’,‘2021-12-31’)*WORKDAY_DIFF({Physical Start Date},‘2021-12-31’))

.

1 reply

Justin_Barrett
Forum|alt.badge.img+21

Welcome to the community, @Rob_Lee1! :grinning_face_with_big_eyes: From what I can see, this formula is just determining which calculation leads to a smaller number. Either

{Annual Base} / 12

or

{Annual Base} / 12 / WORKDAY_DIFF('2021-12-01','2021-12-31') * WORKDAY_DIFF({Physical Start Date},'2021-12-31')

The first thing that I’m inclined to do here is simplify the process. Rather than repeating each sub-formula twice—once each when determining which outputs the larger value, and each again as part of the output depending on the result of the test—you could use the MIN() function, which is designed to return the largest of the submitted values. Here’s that change (formatted on separate lines for clarity):

MIN(
    {Annual Base} / 12,
    {Annual Base} / 12 / WORKDAY_DIFF('2021-12-01','2021-12-31') * WORKDAY_DIFF({Physical Start Date},'2021-12-31')
)

The next simplification that I would suggest considering is the first WORKDAY_DIFF() calculation. That number will always be the same (23), so there’s no need to calculate it every time. That takes us to this:

MIN(
    {Annual Base} / 12,
    {Annual Base} / 12 / 23 * WORKDAY_DIFF({Physical Start Date},'2021-12-31')
)

To avoid an error when there’s no date in {Physical Start Date}, we can add a wrapping IF() function to only do the rest if that date exists:

IF(
    {Physical Start Date},
    MIN(
        {Annual Base} / 12,
        {Annual Base} / 12 / 23 * WORKDAY_DIFF({Physical Start Date},'2021-12-31')
    )
)

The remaining problem is when {Physical Start Date} is in a later year. In that case, my gut feeling is that all year calculations should be based on the year of the physical start date. This can be done by using the YEAR() function to get that year, and prepending it on the other date strings. (This also means replacing the literal 23 with the original WORKDAY_DIFF() function.)

Here’s the final formula with that change applied:

IF(
    {Physical Start Date},
    MIN(
        {Annual Base} / 12,
        {Annual Base} / 12 / WORKDAY_DIFF(
            YEAR({Physical Start Date}) & "-12-01",
            YEAR({Physical Start Date}) & "-12-31"
        ) * WORKDAY_DIFF(
            {Physical Start Date},
            YEAR({Physical Start Date}) & "-12-31"
        )
    )
)
Terms & ConditionsAccessibility statement