Late dates

Samuel Dowdy Jr.

I want to flag (symbol) a cell if the posted date is 3 days later than the end date.

Kelly Moore

Hey @Samuel Dowdy Jr.

This formula says if the difference between the two days is 3 then a flag is turned on, which should translate to what you're asking for. I did notice what I considered 3 days was actually 4 days in this formula. As written, this is an exact answer, I mean if the posted date is more than 3 days the flag will not turn on. Did you want that? Also, if you are getting negative numbers then I misinterpreted which date is the start date and which date is the final date. You would swap them in the formula. Help me understand what the formula is not doing for you so I can adjust the formula. Can you show me a screenshot.

=IF(NETDAYS([End Date - Pay Period]@row, [Posted Date]@row) >= 3, 1)

Kelly Moore

The IFERROR is checking the box. If you want it to be blank replace the last '1' with double quotes ""

If that doesn't work for you, another approach is the IF(AND) which looks for dates in both columns.

=IF(AND(ISDATE([Posted Date@row), ISDATE([Invoice Date]@row), IF(NETDAYS([Invoice Date]@row, [Posted Date]@row) >= 3, 1),"")

Kelly Moore

Hey @Samuel Dowdy Jr.

The purpose of the IFERROR function is to mitigate errors that might occur. In your previous formula, the function was an option because the NETDAYS function will throw an error if dates are not present in a field. We now need to add a nested IF to your IFERROR.

When using an nested IF the main point to remember is that a formula advances through the nested IFs until the first 'true' is reached. Even if subsequent IFs will also be true, it's the first true that governs the formula. Therefore the order of IFs are important. What I understand by your question above (and I'm I'm flipping it), regardless of the number of days between Invoice date and Posted Date, if Status IS approved, the box is NOT checked.

=IF([Invoice Status]@row="Approved", 0, IFERROR(IF(NETDAYS([Invoice Review Due Date]@row, [Posted Date]@row) >= 1, 1), ""))

Does this work for you?

PS - Your formula, as you wrote it above, will now check the box if the difference between dates is 1 day or more. That is different than your original note of 3 days. I'm just checking that is what you intended.

Kelly

Kelly Moore

Hey @Samuel Dowdy Jr.

Try this

=IF(NETDAYS([End Date]@row, [Posted Date]@row) = 3, 1)

Depending on how you are counting the first day (as day 0 or day 1), you may need to equal 4 instead of 3.

Kelly

Samuel Dowdy Jr.

Its not doing what I want

=IF(NETDAYS([End Date - Pay Period]@row, [Posted Date]@row) = 3, 1)

I basically want the flag to show posted date is 3 days past the end date.

Kelly Moore

Hey @Samuel Dowdy Jr.

This formula says if the difference between the two days is 3 then a flag is turned on, which should translate to what you're asking for. I did notice what I considered 3 days was actually 4 days in this formula. As written, this is an exact answer, I mean if the posted date is more than 3 days the flag will not turn on. Did you want that? Also, if you are getting negative numbers then I misinterpreted which date is the start date and which date is the final date. You would swap them in the formula. Help me understand what the formula is not doing for you so I can adjust the formula. Can you show me a screenshot.

=IF(NETDAYS([End Date - Pay Period]@row, [Posted Date]@row) >= 3, 1)

Samuel Dowdy Jr.

What about this formula?

=IFERROR(IF(NETDAYS([Invoice Date]@row, [Posted Date]@row) >= 3, 1), 1)

If my invoice date is blank, then I dont want a flag.

Kelly Moore

The IFERROR is checking the box. If you want it to be blank replace the last '1' with double quotes ""

If that doesn't work for you, another approach is the IF(AND) which looks for dates in both columns.

=IF(AND(ISDATE([Posted Date@row), ISDATE([Invoice Date]@row), IF(NETDAYS([Invoice Date]@row, [Posted Date]@row) >= 3, 1),"")

Samuel Dowdy Jr.

=IFERROR(IF(NETDAYS([Invoice Review Due Date]@row, [Posted Date]@row) >= 1, 1), "")

What about this one? I need it to stay flagged unless the Invoice Status is "Approved"

Kelly Moore

Hey @Samuel Dowdy Jr.

The purpose of the IFERROR function is to mitigate errors that might occur. In your previous formula, the function was an option because the NETDAYS function will throw an error if dates are not present in a field. We now need to add a nested IF to your IFERROR.

When using an nested IF the main point to remember is that a formula advances through the nested IFs until the first 'true' is reached. Even if subsequent IFs will also be true, it's the first true that governs the formula. Therefore the order of IFs are important. What I understand by your question above (and I'm I'm flipping it), regardless of the number of days between Invoice date and Posted Date, if Status IS approved, the box is NOT checked.

=IF([Invoice Status]@row="Approved", 0, IFERROR(IF(NETDAYS([Invoice Review Due Date]@row, [Posted Date]@row) >= 1, 1), ""))

Does this work for you?

PS - Your formula, as you wrote it above, will now check the box if the difference between dates is 1 day or more. That is different than your original note of 3 days. I'm just checking that is what you intended.

Kelly