Google-sheets – Get date from week number in Google Sheets

There may be a more eloquent way, but this is the way I came up with:

The function to find the last populated cell in a column is:

=INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) )

So if you combine it with your current function it would look like this:

=DAYS360(A2,INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ))

Note: when it says "B5" in the explanation below, it actually means "B{current_row}", so for C5 it's B5, for C6 it's B6 and so on. Unless you specify $B$5 - then you refer to one specific cell.

This is supported in Google Sheets as of 2015: https://support.google.com/drive/answer/78413#formulas

In your case, you will need to set conditional formatting on B5.

• Use the "Custom formula is" option and set it to =B5>0.8*C5.
• set the "Range" option to B5.
• set the desired color

You can repeat this process to add more colors for the background or text or a color scale.

Even better, make a single rule apply to all rows by using ranges in "Range". Example assuming the first row is a header:

• On B2 conditional formatting, set the "Custom formula is" to =B2>0.8*C2.
• set the "Range" option to B2:B.
• set the desired color

Will be like the previous example but works on all rows, not just row 5.

Ranges can also be used in the "Custom formula is" so you can color an entire row based on their column values.