Date Calculation Question

mroper84051

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!

 

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)))))))