Novel user - Date formula Hellp

Heather Szukis

Hello, please help I'm fairly new to smartsheet and am stuck. I would love to have my start and end date automatically populate so I do not have to go and add each date on every line as I currently do, one by one. The start and end dates are the same date (for now). This date is not tied to another column. What this date does is when the date is today, it sends an automation to another team to confirm they ran a task. They then check a box and the task is completed. I have a few smartsheets similar to this where I currently add dates so this would be a huge help.

The second picture I need for every other Tuesday but have manually added those for the entire year. But just as reference in case I could use the same formula as for my above question

Genevieve P.

Hi @Heather Szukis

One thing that may help you as you set up your sheets is the drag-fill feature that recognizes patterns.

So, in your first image, it looks like you have Monday - to - Friday listed as your dates, skipping Saturdays and Sundays. You would just need to enter two weeks in, showing Smartsheet the pattern of skipping the weekend. Then highlight the 10 cells, and drag-fill the pattern down the entire column:

This would also work for filling a column with every-other Tuesday.

Once you have 2 Tuesdays in the sheet to show the pattern, you can drag this down the column:

See: Create a Series of Numbers or Dates with Drag-Fill

In regards to auto-filling your End Date, there's a few ways you could do this. You could either then simply copy your entire Start Date column data and paste it into the End Date to have the same date, or you could set up a Record a Date workflow to record the End Date once something happens in the sheet.

Let me know if I've misunderstood what you're looking to do! If I have, it would be helpful to know a bit more about your process (how are you creating the rows).

Cheers,

Genevieve

Genevieve P.

Hi @Heather Szukis

My apologies! You're correct, it's reading the pattern as those specific two weeks and duplicating the dates instead of carrying it on.

In this instance I would recommend filling the entire column with all dates (entering two dates, one after the other, then drag-filling the whole column). Then go through and delete out any of the Saturday and Sunday rows.

If you're not sure what dates are weekend dates, we can add in a helper column to find the WEEKDAY of your dates.

=WEEKDAY([Start Date]@row)

Make this a column formula. Then you can use a Filter to find any rows that have a 1 or a 7 weekday, and delete all those rows.

Would that work for you?

Cheers!

Genevieve

Bruce Case

Hi Heather. Is this what you are attempting?

In this example, I populated the Start Date as today for all rows, then in the duration column, I entered the number of days to add. The End Date of course is the result, using this formula:

=[Start Date]@row + Duration@row

Genevieve P.

Hi @Heather Szukis

One thing that may help you as you set up your sheets is the drag-fill feature that recognizes patterns.

So, in your first image, it looks like you have Monday - to - Friday listed as your dates, skipping Saturdays and Sundays. You would just need to enter two weeks in, showing Smartsheet the pattern of skipping the weekend. Then highlight the 10 cells, and drag-fill the pattern down the entire column:

This would also work for filling a column with every-other Tuesday.

Once you have 2 Tuesdays in the sheet to show the pattern, you can drag this down the column:

See: Create a Series of Numbers or Dates with Drag-Fill

In regards to auto-filling your End Date, there's a few ways you could do this. You could either then simply copy your entire Start Date column data and paste it into the End Date to have the same date, or you could set up a Record a Date workflow to record the End Date once something happens in the sheet.

Let me know if I've misunderstood what you're looking to do! If I have, it would be helpful to know a bit more about your process (how are you creating the rows).

Cheers,

Genevieve

Heather Szukis

Genevieve, thank you this worked great for the bi weekly report and the record date feature is so helpful. I can use this in many instances!

However it did not work for my main example of the M-F. What ended up happening was it just copied and pasted the same dates. See below, any advice? I've tried it many times deleting, adding a few different ways and the same outcome. I added 2 weeks of info; 6-14 work week & 6-21 work week.

Genevieve P.

Hi @Heather Szukis

My apologies! You're correct, it's reading the pattern as those specific two weeks and duplicating the dates instead of carrying it on.

In this instance I would recommend filling the entire column with all dates (entering two dates, one after the other, then drag-filling the whole column). Then go through and delete out any of the Saturday and Sunday rows.

If you're not sure what dates are weekend dates, we can add in a helper column to find the WEEKDAY of your dates.

=WEEKDAY([Start Date]@row)

Make this a column formula. Then you can use a Filter to find any rows that have a 1 or a 7 weekday, and delete all those rows.

Would that work for you?

Cheers!

Genevieve