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

Based on frequency selected change the formula for the created date.
I feel like this should be super simple but I am just not getting it right. Based on Frequency: Daily, weekly, monthly I want to change the next due date This will be calulated from Created column and +1, 7, 30 etc added. It works single instance =IF(Frequency@row = "Daily", Created@row + 1) but as soon as I try to write…
Counting tasks completed on time
I need a formula that says, if the "Complete?" box is checked, was the date in the Actual Completion column on (or before) the date in the Finish column.
Summary sheet values don't update in dashboard without entering source sheet?
I have a sheet summary report on a dashboard and oftentimes the values will not update unless I go into the source sheet and manually save the updates. For example, the WEEK TIME OFF value often will not update week-to-week unless I go into that contact's sheet and save. I have tried adding a daily Chron automation…