Conditional formatting days passed "10 day(s)"

Question

Info in F cell tells us the time stamp. Corresponding G cell shows times passed. Need conditionally formatting G cells for days passed. 2 day(s) 5 day(s) 10 day(s)

Because the formula isn't straightforward I have not found a simple way to conditionally format it.

i only did dates between "1d and 3d", some of it works but when it gets to 10d it screws up, or it's all random.

conditional formatting as it is, wrong

"=IF(DATEDIF(F17, $F$2, "D")>365, QUOTIENT(DATEDIF(F17, $F$2, "D"), 365)&" year(s) "& QUOTIENT(MOD(DATEDIF(F17, $F$2, "D"), 365), 30)&" month(s) "& MOD(QUOTIENT(MOD(DATEDIF(F17, $F$2, "D"), 365), 30), 30)&" day(s)", IF(DATEDIF(F17, $F$2, "D")>30, QUOTIENT(DATEDIF(F17, $F$2, "D"), 30)&" month(s) "& MOD(DATEDIF(F17, $F$2, "D"), 30)&" day(s)", DATEDIF(F17, $F$2, "D")&" day(s)"))"

Here is the link to the spreadsheet.

https://docs.google.com/spreadsheets/d/1b6nXGCqZo82aqIzqvJUlYdgFf0i8WBuJoGK2k792OAk/edit?usp=sharing

Thank you.

Answer 1

This isn't specifically what you asked for in your post (and "player0" has you covered there); but you could also benefit from an array formula in your sheet range G3:G. If you want to try that, delete everything from G3:G and then place the following formula in cell G3:

=ArrayFormula(IF(F3:F="",, IF(DATEDIF(F3:F, $F$2, "Y")<1,, DATEDIF(F3:F, $F$2, "Y")&" year"&IF(DATEDIF(F3:F, $F$2, "Y")=1,, "s")&" ")& IF(DATEDIF(F3:F, $F$2, "M")<1,,MOD(DATEDIF(F3:F, $F$2, "M"), 12)&" month"&IF(MOD(DATEDIF(F3:F, $F$2, "M"), 12)=1,, "s")&" ")& IF(DAY($F$2)>=DAY(F3:F),DAY($F$2)-DAY(F3:F),30-DAY(F3:F)+DAY($F$2))&" day"&IF(IF(DAY($F$2)>=DAY(F3:F),DAY($F$2)-DAY(F3:F),30-DAY(F3:F)+DAY($F$2))=1,, "s")))

This will return all results for all non-null rows.

It will also be "cleaner" (e.g., adding "s" to units only when needed) and more accurate than your original formulas.

Answer 2

try:

=F$2-F3<3

=F$2-F3<6

=F$2-F3<10

=F$2-F3<16

demo sheet