Best Of

Re: Automation to copy row at specific dates

If you can make a list of every date, you can use a formula to grab the correct date and put it into a column and leverage that in an automation. Granted, creating and maintaining the list is a manual process, but at least you only have one place you have to put each date one time. Then you would use

=MIN(COLLECT({Date Sheet Date Column}, {Date Sheet Date Column}, @cell >= TODAY()))

Then you would set up an automation to run daily with a condition of this helper column being equal to today.

Or is it different dates for different rows?

Paul Newcome

1 ·

Re: Calculating the difference in years, months, and days between two date ranges

I wish Smartsheet had this functionality like Excel's DATEDIF function. I had to resort to creating formulas from scratch to replicate it. There are separate formulas to calculate the years, months-in-excess-of-years, and days-in-excess-of-months. Create 3 columns 'Years', 'Months', 'Days'. The following example uses today's date for calculation (7/30/2024). If you have static End Dates, create an End Date column and replace TODAY() with its reference cell.

Smartsheet Example

Excel Example using DATEDIF()

Years column formula:

=IF(DAY(TODAY()) < DAY([Start Date]@row), (IF(MONTH(TODAY()) <= MONTH([Start Date]@row), YEAR(TODAY()) - YEAR([Start Date]@row) - 1, YEAR(TODAY()) - YEAR([Start Date]@row))), (IF(MONTH(TODAY()) < MONTH([Start Date]@row), YEAR(TODAY()) - YEAR([Start Date]@row) - 1, YEAR(TODAY()) - YEAR([Start Date]@row))))

Months column formula:

=IF(DAY(TODAY()) < DAY([Start Date]@row), (IF(MONTH(TODAY()) <= MONTH([Start Date]@row), MONTH(TODAY()) - MONTH([Start Date]@row) + 11, MONTH(TODAY()) - MONTH([Start Date]@row) - 1)), (IF(MONTH(TODAY()) < MONTH([Start Date]@row), MONTH(TODAY()) - MONTH([Start Date]@row) + 12, MONTH(TODAY()) - MONTH([Start Date]@row))))

Days column formula:

=IF(DAY(TODAY()) >= DAY([Start Date]@row), DAY(TODAY()) - DAY([Start Date]@row), IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 4, MONTH(TODAY()) = 6, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9, MONTH(TODAY()) = 11), 31 - DAY([Start Date]@row) + DAY(TODAY()), IF(OR(MONTH(TODAY()) = 5, MONTH(TODAY()) = 7, MONTH(TODAY()) = 10, MONTH(TODAY()) = 12), 30 - DAY([Start Date]@row) + DAY(TODAY()), IF(AND(MONTH(TODAY()) = 3, MOD(YEAR(TODAY()), 4) = 0), 29 - DAY([Start Date]@row) + DAY(TODAY()), IF(AND(MONTH(TODAY()) = 3, MOD(YEAR(TODAY()), 4) <> 0), 28 - DAY([Start Date]@row) + DAY(TODAY()), "ERROR.")))))

Years, Months, Days Concat formula:

=Years@row + " Year" + IF(Years@row = 1, ", ", "s, ") + Months@row + " Month" + IF(Months@row = 1, ", ", "s, ") + Days@row + " Day" + IF(Days@row = 1, "", "s")

I believe this will do what you are looking for and also accounts for leap years. I tested a wide range of dates, so this should work exactly as Excel's DATEDIF().

smdinhphan

1 ·

Re: Sort Card View to have High Priority on the top

hi @Isaac A. ,

thank you for your answer and link. I will vote it up.

kowal

1 ·

Re: Combine (Concatenate) Text or Values from Two or More Cells into Dropdown List Column

You would use this as your delimiter:

CHAR(10)

Paul Newcome

1 ·

Re: Smartsheet & Outlook Invites for meetings

Hi @mhint2005 - we got your enquiries and responded to both, if you're not seeing our replies, please reach out directly to me on lindsay@infospark.com.au

Cheers,

Lindsay

Lindsay Whitbread

1 ·

Re: Smartsheet Profile

Hi @Sherry Fox

Thanks for the suggestion! This is the first time a secondary email has been suggested for Community specifically, and it has not yet been explored as a possibility. If you have a secondary email on your Smartsheet account (such as your personal one), then you can make this your Primary email and use it to sign in to the same Community profile that way. Here's the article on Primary and Secondary emails in Smartsheet.

Please feel free to post this as an Idea the Community Ideas topic so other members can vote on it and provide their feedback as well!

Cheers,

Genevieve

Genevieve P.

1 ·

May Question of the Month - Join the conversation and receive a badge

Hey Community!

Last month we asked about your desk treasures, and it was a joy to read all the stories behind them. There was a bit of everything — a 3D printed dragon, your kid’s sweet notes, more than a few coffee mugs, and fun coworker gifts (like this wooden frog)! I loved seeing you connect while sharing how much meaning these little objects can hold.

Now, let’s switch it up again. Here’s a fun new chance to earn a badge and get to know your peers along the way.

How would you describe your job to a five year old?

I’m really looking forward to seeing what you come up with! Let's see how you break down your job for the little ones.

Share those kid-friendly explanations in the comments below. ⬇️

About Question of the Month:

We kick off each month with a new question. These monthly ice breakers are just one way to help you get to know your Smartsheet peers and rack up some badges and points in the process.

Everyone who participates by answering the question prior to the end of the month will automatically receive a unique participant badge once the question closes at the end of the month.

In addition to the participant badge, the top three answers based on Community member votes will receive the Top Answer badge. To vote on an answer click on the upvote icon associated with an answer in the comments below. Multiple votes allowed and encouraged!