How do I fill a date series from left to right?
I found an answer with an incredibly complicated formula for text fields, but I wanted to ask again just to be certain  is there no way to dragfill a date series from left to right in Smartsheets? I don't want the entire column to be dates, just the one row, so you can see that Week 25 is the week of 6/19/23, for instance. So far the only answer I've come up with is to do it in another program where it's easy and then copy the information into Smartsheets, and that seems like that can't be the only option. Is there a formula to return the Monday of a numbered week of the year? There has to be SOME way to do this inside of Smartsheets that isn't painful, I just haven't figured out what it is yet.
Best Answers

I missed a small piece in my formula. Is there a face palm emoji? Ugh. There should also be a $ before the first cell reference to lock that one in as well to keep it from updating during dragfill.
=($[W1]@row + (7 * COUNT($[W1]@row:[W1]@row))) + ""

💥 BOOM! 💥 That did it, my friend! Thank you so much, @Paul Newcome!
Answers

If I am understanding correctly you want to add 7 days to the date in the column cell before the cell it is going into example below
If that is the case you would only need to put a date in the first column then put the formula =[Column2]@row + 7 in the next column then you could copy that column over as far as you want and it will add 7 days to the date

Yes! You've understood me correctly! I guess the problem I keep running into is that on an existing sheet, if that column isn't formatted to be a Date column, then you get 6/19/23 into 6/19/237 instead of 6/26/23. Actually, it's doing the same thing on a brand new sheet too.

Basically I'm looking to do this sequence, where Week 25 displays the date for the Monday of that week, but without having to have every column in this sequence be formatted to be dates.

As long as the first column is set as a date type column, you can use something like this:
=([Actual Date Column]@row + (7 * COUNT($[Actual Date Column]@row:[Actual Date Column]@row))) + ""
Just make sure you get that $ tucked into the right place. Drop the formula into the column immediately to the right of the actual date column then dragfill on over to the right as far as you need to go.

Hm  sort of works, but it only does it once, i.e., if you put 1/2/23 in the first cell, all the rest are 1/9/23.
Here's the formula I cooked up:
=([W1]@row + (7 * COUNT($[W1]@row:[W1]@row))) + ""
so possibly I misunderstood what goes where.
It does correctly return 1/9 the first time but that's all it does. (The top row is just where I copied & pasted from a spreadsheet program.)

Did you dragfill the formula across by hovering over the bottom right corner of the cell. clicking, then dragging over?
There are two key points to this working. The dragfill and this part:
COUNT($[W1]@row:[W1]@row)
More specifically the first cell reference in the range being locked and the second is not. So when you dragfill, the first part of the range should stay
$[W1]@row
and the second part of the range
[W1]@row
should automatically adjust to be the cell immediately to the left.
So if [W1] is the date type column, you would put the formula into [W2]. When you dragfill to the right by one column, it should change from
=([W1]@row + (7 * COUNT($[W1]@row:[W1]@row))) + ""
to
=([W1]@row + (7 * COUNT($[W1]@row:[W2]@row))) + ""
so on and so forth.

I am using dragfill, yes. So that part we're square on.
I expanded the columns though and realized it's actually doing this:
So it's not just returning the first date, it's doing the math but not returning it as dates?

That's where having the first colum set as a date type column comes into play. If needed, you can insert a (hidden) date type column to the left and reference that as your starting point.

W1 is a Date column but not restricted to dates  would that make the difference?

I missed a small piece in my formula. Is there a face palm emoji? Ugh. There should also be a $ before the first cell reference to lock that one in as well to keep it from updating during dragfill.
=($[W1]@row + (7 * COUNT($[W1]@row:[W1]@row))) + ""

💥 BOOM! 💥 That did it, my friend! Thank you so much, @Paul Newcome!

Happy to help. 👍️
And sorry again for missing that $ the first time around.

No apologies necessary! You got me there in the end and that's what counts.
Help Article Resources
Categories
Check out the Formula Handbook template!