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

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

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?

Tagged:

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, [email protected])

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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[email protected] ✭✭✭✭✭

    =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.

  • 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 @[email protected],

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

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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.

Sign In or Register to comment.