Tracking Previous Week Order Completions vs This Week

Jen Castillo

Hello,

This feels like a simple question, but the answer is eluding me. We have dates getting automatically logged for when orders come in/are delivered, and then another automation to log the date when the inspection is completed.

I need to have two different results. One for when an order delivered from the previous week is completed this week, and then a different one for orders delivered/completed in one week.

How would I go about this? Thank you in advance!

Jen Castillo

This worked perfectly! Thank you so much!

Is it possible to add in another section for orders received 2 weeks back? I don't think we'll be tracking things like that, but just in case.

Tomasz Giba

Hi @Jen Castillo,

This formula will give you number of weeks difference between any two given dates:

=IFERROR(IF(WEEKNUMBER(Completed@row) < WEEKNUMBER(Date@row), WEEKNUMBER(Completed@row) + 52 - WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Completed@row) - WEEKNUMBER(Date@row) + 1), "")

Tomasz Giba

Hi @Jennifer Castillo

I'm not sure how this will work in your case but you may check this column formula:

=IF(AND(NETDAYS(Delivery@row, TODAY()) + 1 > WEEKDAY(TODAY()), ISDATE(Completed@row)), 2, IF(ISDATE(Completed@row), 1, ""))

This will return:

• blank if there is no completed date,

• 1 - if started and completed in this week,

• 2 - if started in previous and completed in this week.

Please let us know if you need further adjustments.

Jen Castillo

This worked perfectly! Thank you so much!

Is it possible to add in another section for orders received 2 weeks back? I don't think we'll be tracking things like that, but just in case.

Tomasz Giba

Hi @Jen Castillo,

This formula will give you number of weeks difference between any two given dates:

=IFERROR(IF(WEEKNUMBER(Completed@row) < WEEKNUMBER(Date@row), WEEKNUMBER(Completed@row) + 52 - WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Completed@row) - WEEKNUMBER(Date@row) + 1), "")

Jen Castillo

https://community.smartsheet.com/discussion/comment/339407#Comment_339407

Thanks so much, this worked perfectly!