Drag date and ignore hidden cells

[Jeorge]

Hi Sir Ma'am,

Need help to put the dates only on visible cells, I hid A2, A3 then hid A5, A6 then hid A8, A9 and so on... With that my visible cells were A1, A4, A7, A10.
A1= 1-Apr and I wanted to drag it to A10 and my expectation was
A1 = 1-Apr
A4 = 2-Apr
A7 = 3-Apr
A10 = 4-Apr
but when I drag it, hidden cells are still considered which failed my date that I wish to see only on visible cells.

Thank you very much for any help

 

[footoo]

Enter in A1 : =SUBTOTAL(103,$B$3:$B3)+DATEVALUE("31/3/20")
Drag down.

 

[footoo]

Sorry, that should be : =SUBTOTAL(103,$B$1:$B1)+DATEVALUE("31/3/20")

 

[Peter_SSs]

Sorry, that should be : =SUBTOTAL(103,$B$1:$B1)+DATEVALUE("31/3/20")

Caveat: That does require B1, B4, B7 and B10 all to be non-empty or unusual results will appear.

 

[Jeorge]

Ho Footoo, Peter,

I placed this formula =SUBTOTAL(103,$B$1:$B1)+DATEVALUE("31/3/20") on A1 and I also anticipate Peter's info and this is what I got:

 

[footoo]

Caveat: That does require B1, B4, B7 and B10 all to be non-empty or unusual results will appear.

Thanks for pointing out.

 

[footoo]

Ho Footoo, Peter,

I placed this formula =SUBTOTAL(103,$B$1:$B1)+DATEVALUE("31/3/20") on A1 and I also anticipate Peter's info and this is what I got:

View attachment 10043

That's strange.
Try entering the date in A1, and in A4 : =SUBTOTAL(103,$B$1:$B4)+$A$1-1
Drag A4 down

 

[Peter_SSs]

placed this formula =SUBTOTAL(103,$B$1:$B1)+DATEVALUE("31/3/20") on A1 and I also anticipate Peter's info and this is what I got:

Could relate to your regional date settings. Try this instead.

=SUBTOTAL(103,$B$1:$B1)+DATE(2020,3,31)

 

[jasonb75]

Another way,

=DATE(2020,4,(ROWS(A$1:A1)-1)/3+1)

Remember to select visible cells only otherwise the formula will be entered into the hidden rows as well.

 

[Peter_SSs]

Another way,

=DATE(2020,4,(ROWS(A$1:A1)-1)/3+1)

Remember to select visible cells only otherwise the formula will be entered into the hidden rows as well.

I'm not saying that the OP may not be able to use this in some way but it does not address this requirement

I wanted to drag it ...

or this one if a different set of rows are hidden

only on visible cells.