Google-sheets – % Complete based off dates

google sheets

I have a project start date and end date. I want to create a formula that says looking at todays date, here is what % we are into the project. Any way to do this via Google sheets?

Best Answer

It's probably easier than you think. You can do arithmetic operations on dates. You may need to tweak formatting, but this should work:

  |     A      |     B     |
----------------------------
1 | start      | end       |
2 |   1/3/2017 | 5/31/2017 |

Assuming your start date is in A2, and your end date is in B2...

The difference between your dates can be expressed as =B2-A2.

The difference between your start date and the current date can be expressed as =TODAY()-A2.

Using the division of the two will give you the ratio: =(TODAY()-A2)/(B2-A2). (Don't forget the parentheses, otherwise you'll have an order of operations problem.) Then it's just a question of formatting your output as a percentage. (If you want to shortcut it and not worry about formatting, simply multiply the result by 100.)

Related Solutions

Google-sheets – Conditional counts based on dates

The functions you need to use are SUMIF and COUNTIF.

For example, to count all the bulbs with DateFilterCleaned within six months, and assuming that DateFilterCleaned is within six months, you need to:

Make a helper-column which calculates the number of days eg E3 = now()-D3

And then a formula like this =COUNTIF(E:E, ">183")

To make it general purpose for each school year, just put the start-date and end-date into separate cells (eg F1 and F2) and refer to them using absolute addressing (eg $f$1 and $f2)

(This isn't the full solution, but is hopefully enough hints to get you started. Note that there is absolutely no need to use scrips for any of this.)

Google-sheets – Query or filter the data where the start time is past 3:00 pm

The query would be like this:

=query(A:D, "select * where C > timeofday '15:00:00' ")

(or >= if you want to include 3 PM sharp)

Here, the single-quoted string 15:00:00 is preceded by the keyword timeofday, indicating its meaning. This is similar to how date strings are preceded by date. Note that the format of query string is fairly rigid: it has to be 24-hour time, with the seconds field included (HH:mm:ss). However, the time data in your spreadsheet can be in whatever format you want.

See scalar functions for more examples of manipulation with timeofday values.

Best Answer

Related Solutions

Google-sheets – Conditional counts based on dates

Google-sheets – Query or filter the data where the start time is past 3:00 pm

Related Topic