Date Calculation Question

Options

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!

 

Comments

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 04/03/19
    Options

    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

    x.jpg

  • mroper84051
    Options

    Thank you.   This will work!

  • djpreece
    djpreece ✭✭
    edited 04/05/19
    Options

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

     

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!