Round date down to the nearest Monday

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.
Best Answer
-
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?
Answers
-
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:
- =[Column1]@row - (WEEKDAY([Column1]@row) - 1)
- =[Column1]@row - WEEKDAY([Column1]@row)
-
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?
-
Hello Connie,
Is the column type 'date'?
-
The column with the date is a date
-
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?
-
I was able to figure a way around it but thank you for your help.
Help Article Resources
Categories
Check out the Formula Handbook template!