Round date down to the nearest Monday

Connie Cochran

I have a column of dates and I need to create a column that would use that date to round down to the nearest Monday so i can count how many lines of data for a specific week with a date. I was able to get the week number but really need the date.

Paul Newcome

Are you able to provide a screenshot of the source data? Is the column you are putting the formula in also set as a date type column? How is your date column currently being populated?

Lachlan Stead

To return the prior Monday of any given date, the easiest way is probably to use the weekday formula and another calculation... if you've configured Smartsheet to start each week on a Sunday, use the first option below, otherwise the second if configured for weeks to begin on a Monday:

1. =[Column1]@row - (WEEKDAY([Column1]@row) - 1)
2. =[Column1]@row - WEEKDAY([Column1]@row)

Connie Cochran

Thank you for your help,

Is this correct? the column that has the date in it is "Approved screening date" so in a separate column I should add the formula =[Approved screening date]@row - (WEEKDAY([Approved screening date]@row) - 1)

I'm getting an error stating #invalid column

So I tried it on the column that I created the worknumber and =[Week of first event]@row - (WEEKDAY([Week of first event]@row) - 1)

received an error state #invalid data type

What am i doing wrong?

Lachlan Stead

Hello Connie,

Is the column type 'date'?

Connie Cochran

The column with the date is a date

Paul Newcome

Are you able to provide a screenshot of the source data? Is the column you are putting the formula in also set as a date type column? How is your date column currently being populated?

Connie Cochran

I was able to figure a way around it but thank you for your help.