|Second question first:|
Look up "relative" and "absolute" references in the Excel Help files.
Absolute references don't change when you drag a formula, Relative references do.
Your references are all Relative. To change an reference to Absolute, put a $ in front of the Column letter, the Row number, or both. References with $ in front of them don't change:
Only S2 will change when you drag this.
As hinted at above, you can lock just the Column or just the Row.
When entering the formula, if you highlight the range e.g. B2:B48577 and press F4, it will rotate through the different Absolute and Relative reference choices.
As far as dragging the formula to pick up different dates, why not put your 55 dates in a list and reference that list?
With a list of dates in A1:A55, you can drag this down and only A1 will change since it is the only Relative reference.