First Day of the Prior Month and Last Day of Current Month

Hello! I have a date (A below) that pulls as expected. In B, I'd like to grab the first day of the month for the prior month and then C would be the last day of A. I have room for additional helper columns as needed to make it work. Thanks, Community!


Best Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Answer ✓

    Hi cghallo_UCDenver, 

    You can try this formula for the first day of the prior month- 

    =DATE(YEAR([Shift Date]@row), MONTH([Shift Date]@row) - 1, 1) 

    And for the last day of the current month- 

    =IFERROR(DATE(YEAR([Shift Date]@row), MONTH([Shift Date]@row) + 1, 1), DATE(YEAR([Shift Date]@row) + 1, 1, 1)) - 1 

    I hope this helps!

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • cghallo_UCDenver
    Answer ✓

    Thank you, @Kaveri Vipat...it worked as expected - much appreciated!

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Answer ✓

    Hi cghallo_UCDenver, 

    You can try this formula for the first day of the prior month- 

    =DATE(YEAR([Shift Date]@row), MONTH([Shift Date]@row) - 1, 1) 

    And for the last day of the current month- 

    =IFERROR(DATE(YEAR([Shift Date]@row), MONTH([Shift Date]@row) + 1, 1), DATE(YEAR([Shift Date]@row) + 1, 1, 1)) - 1 

    I hope this helps!

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • cghallo_UCDenver
    Answer ✓

    Thank you, @Kaveri Vipat...it worked as expected - much appreciated!

  • @Kaveri Vipat, I tried to replicate using the above, which worked. Can you please provide the syntax if, in Check Date, I wanted the last day of the next month of the Earnings End Date. For example, the 7/1/23 should be 7/31/23. Thank you!

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    Hi cghallo_UCDenver,

    I am glad it worked, You can try this formula to get the last day of next month.

    =IFERROR(DATE(YEAR([Earnings End Date]@row), MONTH([Earnings End Date]@row) + 2, 1), DATE(YEAR([Earnings End Date]@row) + 1, 1, 1)) - 1

    I hope this works for you!

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!