Date Calculation Question

Can anyone share with me on how I can calculate what I call the 'Next Quarter Projects'? For example:

I have a start date and end date columns for each project, and I want to create a Filter/View that shows me the projects that are due in between a date range(ie Q2, Q3, Q4, etc)

Can anyone help? Thanks!

Find more posts tagged with

Calendar View

Formulas

Conditional Formatting

Comments

L_123

you can pursue this 2 ways. You can create filters with 2 criteria that check which quarter it is, and share the filter, or you can create a helper column that posts which quarter the dates are and filter based off of that. This can get complicated if you want to show which % of the project is in each quarter for projects that are in multiple quarters. I've attached the most basic possible version of the first option. (keep in mind this will omit multi quarter projects.)

Edit: whoops picked wrong end date for the picture. you get the idea though

mroper84051

Thank you. This will work!

djpreece

I did this for calculating man months in each month from a arbitrary start/end date pair and allocation...it's a pain,....you have to pay attention to dates that are within, or cross or encompass month boundaries... (I'm sure it can be optimized)...enjoy..

JANUARY

IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(OR(ISBLANK([Start Date]@row), ISBLANK([Assigned To]@row), Duration@row < 1), 0, IF(OR([Start Date]@row >= DATE(Year@row, 2, 1), [End Date]@row < DATE(Year@row, 1, 1)), 0, IF(AND([Start Date]@row >= DATE(Year@row, 1, 1), [End Date]@row < DATE(Year@row, 2, 1)), NETWORKDAYS([Start Date]@row, [End Date]@row) * [% Allocated]@row / 21.66, IF(AND([Start Date]@row < DATE(Year@row, 1, 1), [End Date]@row >= DATE(Year@row, 2, 1)), NETWORKDAYS(DATE(Year@row, 1, 1), DATE(Year@row, 2, 1) - 1) * [% Allocated]@row / 21.66, IF([End Date]@row >= DATE(Year@row, 2, 1), NETWORKDAYS([Start Date]@row, DATE(Year@row, 2, 1) - 1) * [% Allocated]@row / 21.66, IF([Start Date]@row < DATE(Year@row, 1, 1), NETWORKDAYS(DATE(Year@row, 1, 1), [End Date]@row) * [% Allocated]@row / 21.66, 0))))))

FEBRUARY

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(OR(ISBLANK([Start Date]@row), ISBLANK([Assigned To]@row), Duration@row < 1), 0, IF(OR([Start Date]@row >= DATE(Year@row, 3, 1), [End Date]@row < DATE(Year@row, 2, 1)), 0, IF(AND([Start Date]@row >= DATE(Year@row, 2, 1), [End Date]@row < DATE(Year@row, 3, 1)), NETWORKDAYS([Start Date]@row, [End Date]@row) * [% Allocated]@row / 21.66, IF(AND([Start Date]@row < DATE(Year@row, 2, 1), [End Date]@row >= DATE(Year@row, 3, 1)), NETWORKDAYS(DATE(Year@row, 2, 1), DATE(Year@row, 3, 1) - 1) * [% Allocated]@row / 21.66, IF([End Date]@row >= DATE(Year@row, 3, 1), NETWORKDAYS([Start Date]@row, DATE(Year@row, 3, 1) - 1) * [% Allocated]@row / 21.66, IF([Start Date]@row < DATE(Year@row, 2, 1), NETWORKDAYS(DATE(Year@row, 2, 1), [End Date]@row) * [% Allocated]@row / 21.66, 0)))))))

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…