Formula to calculate days between two potential start dates

I have a basic report request form and would like to know if I was 'late' in completing the request, which would be simple - but I have the client provided due date column and an adjusted due date column that I enter (to balance impossible timelines - and i want to keep the original request intact).

How can I see if my 'Done' date is negative working days after the 'Due' date OR a potential 'Adjusted Due' date - if there is one.

In other systems I'd do a coalesce of the adj due, due columns then compare against the done date. Feel like I may be missing something simple here.

Poorly drawn on screenshot - if that helps:

Thanks!!

Find more posts tagged with

Formulas

Accepted answers

All comments

Sing C

Hi @Jennifer Locke,

If I understand your question correctly, you want to compare the 'done date' with 'due date' if there is no 'adj due date' but if there is an 'adju due date' the comparison should be 'done date' against 'adj due date' to see if the request was completed late.

I'd recommend a column (you may have it outside of the screenshot) that calculates the "Late" status of the request and use an IF formula to set the value of the column depending on the blank status of the 'Adj Due Date' column.

I quickly mocked up the below based on the two requests you highlighted, with the first request duplicated (removing the Adj Due Date) to show that it would have been late against the original due date. The formula I used in the 'Late?' column is:

=IF(ISBLANK([Adj Due Date]@row), IF([Done Date]@row > [Due Date]@row, "Y", "N"), IF([Done Date]@row > [Adj Due Date]@row, "Y", "N"))

Let me know if this helps!

Thanks,

@Sing C

Jennifer Locke

Hi Sing,

This sort of answers my question - my goal was to see how many days over the due (regular OR adjusted if applicable) date I came in at. I see your logic, but I'd still like to have the metrics on how much more time I'd needed to complete the task.

But you gave me a great starting point and I think I'm 98% there

=IF(ISBLANK([Adj Due Date]@row), SUM([Days Given to Complete]@row - [Completed In (days)]@row), SUM([Adjusted Days Given]@row - [Completed In (days)]@row))

when I tried to use the date fields, it would add a day automatically, not giving me a real view of timing. So, I applied the formula to the three columns I have counting days between for simpler math and it seems to be working.

Thank you for your suggestion which led to much more effective experiments, so I could get so much closer to my goal.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.