Formula to capture data in a cell on last day of the month.

Tina Ciak
Tina Ciak ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I'm trying to figure out a formula that will capture the data ($) that is in a cell (on another sheet) on the last day of each month. Is this even possible?

These are items that will move/change frequently so I just need to lock in the numbers at the end of each month and would prefer it to not have to be manual.

Can anyone help?

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Tina,

    Will the cell change after the last day of the month.

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi

    Can it be overwritten on the last day of the next month?

    i.e. so it will always show the value from the last day of the previous month (if you see what I mean).

    Kind regards

    Debbie

  • L_123
    L_123 ✭✭✭✭✭✭

    =date(year(today()),month(today()),1)-1

    The above will show the last day of the previous month. You can use many different variations of this formula to achieve different things in smartsheet. Depending on how you have your sheet arranged, if you have a date column that shows every date in the year, you could do something like the below

    =index([Return KPI]:[Return KPI], match(date(year(today()),Month(today()),1)-1,Date:Date,0))

    this will return the value in the "Return KPI" column that corresponds to the last day of the previous month.

  • Tina Ciak
    Tina Ciak ✭✭✭✭✭

    Hi everyone,

    Thanks for your comments above. It's taken me a while to get back to this-- I am temporarily using a smartsheet workflow where it sends me a snapshot of the totals at the end of each month.

    Let me try to explain a little better.

    We have a savings pipeline with 5 lanes. We need to capture what the total is in each lane in dollars at the end of each month. These totals are constantly changing, so it's imperative that I capture the total at the end of the day on the last working day of the month so I can report that data to my team. So far I have not found a way to pull this info in and just lock it. If I use a formula, the numbers will continue to change, if I use cell linking, the numbers will continue to change. I'm hoping to find a way to fill in my chart without having to do it manually each month. Am I missing something?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Tina@Masco,

    Would it work to copy the rows automatically to another sheet instead?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I was thinking the same thing. A date type column to establish the last day of the month drives the trigger for a copy row automation.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!