Late dates
I want to flag (symbol) a cell if the posted date is 3 days later than the end date.
Best Answers
-
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)
-
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),"")
-
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
Answers
-
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
-
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.
-
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)
-
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.
-
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),"")
-
=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"
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!