BUG: misleading message when using to_datetime with format='%V %a'
- [ ] closes #50308 (Replace xxxx with the GitHub issue number)
- [ ] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.

Good call @mroeschke to factor this out! I think it's simpler like this, and it helped find some untested code, and other code where the message was misleading

---

Performance is unaffected:

On this branch:
```python
In [1]: format = '%Y-%d-%m %H:%M:%S%z'

In [2]: dates = pd.date_range('1900', '2000').tz_localize('+01:00').strftime(format).tolist()

In [3]: %%timeit
   ...: pd.to_datetime(dates, format=format)
   ...: 
   ...: 
234 ms ± 470 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)
```

upstream/main (after having recompiled):
```python
In [1]: format = '%Y-%d-%m %H:%M:%S%z'

In [2]: dates = pd.date_range('1900', '2000').tz_localize('+01:00').strftime(format).tolist()

In [3]: %%timeit
   ...: pd.to_datetime(dates, format=format)
   ...: 
   ...: 
234 ms ± 885 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)
```
Thanks @MarcoGorelli 