Time Spent (DAY)

Options

Hello-

Can you assist me with my formula?

I need to add in my processing time (Days) column, the Date Recorded Linked.

So basically, I don’t have Date Recorded Completed if I have Date Recorded Linked and vice versa. Do I need an OR formula?


=IFERROR(NETWORKDAYS([Date Started]@row, [Date Recorded Completed]@row), "")


Best Answer

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    Got it. The formula above should achieve the count using that logic. If there is no date in the [Date Recorded Linked] field, the formula will count the number of workdays between the [Date Started] and the [Date Completed Linked]. Otherwise, it will count the number of days between the [Date Started] and the [Date Recorded Linked].

Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    If I follow right, you want to know the number of workdays between the [Date Started] and the [Date Recorded Linked] OR the [Date Recorded Completed].

    This should work for you:

    =IFERROR(IF(ISBLANK([Date Recorded Linked]@row), NETWORKDAYS([Date Started]@row, [Date Completed Linked]@row), NETWORKDAYS([Date Started]@row, [Date Recorded Linked]@row)), "")

  • Marilen.Navarro103391
    Marilen.Navarro103391 ✭✭✭✭✭
    edited 11/17/23
    Options

    @Danielle Arteaga

    I wanted to count the # of Workday between Date Started and Date Recorded Linked, Now if the Date Recorded Linked is blank, I wanted the count the # of Workday between Date Started and Date Recorded Completed.

    Thank you.

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    Got it. The formula above should achieve the count using that logic. If there is no date in the [Date Recorded Linked] field, the formula will count the number of workdays between the [Date Started] and the [Date Completed Linked]. Otherwise, it will count the number of days between the [Date Started] and the [Date Recorded Linked].

  • Marilen.Navarro103391
    Options

    @Danielle Arteaga

    Just a follow up question, how about if I have 5 column dates recorded. Only one recorded date will appear on the 5 columns. What's my formula on my processing Time (Days). This is a different scenario.... I need to include the 3 below in my formula.

    Date Recorded Pipeline

    Date Recorded for PO Submitted

    Date Recorded for Others- Pricing/Freight

    =IFERROR(IF(ISBLANK([Date Recorded Quote]@row), NETWORKDAYS([Date Started]@row, [Date Recorded CE Leads]@row), NETWORKDAYS([Date Started]@row, [Date Recorded Quote]@row)), "")

    Thank you.


  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    Place this formula in the column where you want to calculate the number of days since [Date Started]


    =IFERROR(IF(NOT(ISBLANK([Date Completed Linked]@row)), NETWORKDAYS([Date Started]@row, [Date Completed Linked]@row), IF(NOT(ISBLANK([Date Recorded Linked]@row)), NETWORKDAYS([Date Started]@row, [Date Recorded Linked]@row), IF(NOT(ISBLANK([Date Recorded Pipeline]@row)), NETWORKDAYS([Date Started]@row, [Date Recorded Pipeline]@row), IF(NOT(ISBLANK([Date Recorded for PO Submitted]@row)), NETWORKDAYS([Date Started]@row, [Date Recorded for PO Submitted]@row), NETWORKDAYS([Date Started]@row, [Date Recorded for Others- Freight/Pricing]@row))))), "")


    NOTE: Another way to do this may be by using a the Change Cell Value automated workflow: https://help.smartsheet.com/articles/2482299-change-cell-value-in-an-automated-workflow

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!