Formula to Populate a Month Column Based on a Start and End Date

AACG
AACG ✭✭✭

Hi,

I need to create a formula to provide project analysis. I have a project Start date, a project End date, and a Total Team count. I need to show the Total Team count in the month columns related to active months of the project.

For example, The project start date is 10/23/23 and the project end date is 12/15/23. The team count is 5. I have columns for Jan through Dec.

I need the Oct, Nov and Dec columns to show that 5 team members are needed in Oct, Nov and Dec.

Any help would be very appreciated. I have been struggling with it using a variety of formulas. 😊

Best Answer

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    @AACG You will need to create different formulas per column, based off the month. But here is the one for September

    =IF(AND(PStart@row<=9,PEnd>=9),[Total Team}@row, "")

    Just change the 9 to 10 for October, and so on.

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    @AACG You will need to create different formulas per column, based off the month. But here is the one for September

    =IF(AND(PStart@row<=9,PEnd>=9),[Total Team}@row, "")

    Just change the 9 to 10 for October, and so on.

  • AACG
    AACG ✭✭✭

    Eric, thank you! I knew it was something with If/And but just couldn't get it to work. I really appreciate you taking the time to assist me with it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!