How do I fill a date series from left to right?

Jessica Weiss

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 drag-fill 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.

Paul Newcome

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))) + ""

Jessica Weiss

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

Hollie Green

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

Jessica Weiss

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.

Jessica Weiss

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.

Paul Newcome

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.

Jessica Weiss

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.)

Paul Newcome

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.

Jessica Weiss

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?

Paul Newcome

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.

Jessica Weiss

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

Paul Newcome

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))) + ""

Jessica Weiss

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

Paul Newcome

Happy to help. 👍️

And sorry again for missing that $ the first time around.

Jessica Weiss

No apologies necessary! You got me there in the end and that's what counts.