Forum menu
Hi
I have a spreadsheet that i am trying to format the overdue status
A1 has the Date
A2 Has Value either Yes or Pending
A3 has Days overdue (here i want it to show days over due if the value in A2 is "pending" but if the Value is A2 is yes i want it show "0"
ive been ghitting a brick wall for the past hour
What is creating your days pending value?
=If(A2="yes",0,whatever your current formula is)
=today()-A1:A
=If(A2=”yes”,0,today()-A1)
I think I can improve it a bit.
=If(A2=”yes”,0,if(today()-A1<14,today()-A1,"send the boys round")
How reliable is the "yes" bit? Could it be "yes", "yes ", "Yes", "YES" (in a When Harry Met Sally stylee) - if so use
=IF(TRIM(LOWER(A2))="yes",0, TODAY()-A1)
Excel formulas tend to be less critical regarding capitalisation of text nowadays.
Of course you could always use cell validation to ensure only the prescribed list options are used - possibly overkill.
I like @joshvegas version, just needs a little tweak:
=If(A2=”yes”,0,if(today()-A1<14,"have a quiet word",”send the boys round”))
=If(A2=”yes”,0,if(today()-A1<14,“have a quiet word”,”send the boys round”))
I only dabble in excel. You've completed it haven't you.
My job means I pretty much live in Excel! I can sort you out a macro to email the offenders with a copy statement attached if you like …
excel, should be banned, get some AI SQL on it