April or May

April is busy in Spain with Spaniards celebrating Easter (though it is late March in 2024), Feria in the south. Which can be interesting, but also busy and sometimes ups lodging costs. Barcelona is crowded either month.
Less of an issue on Madrid, but if you have plans for Andalucia (Sevilla, Granada etc) global warming has hit hard and it is not uncommon to have temps of 104F in mid April. I always recommend checking historical data websites for the actual day to day temps and precipitation for each city for the time period of travel for the past 5 years. Average temps are useless these days.

I'd do early May.

I'd be surprised if the water is warm enough for a real trip to the beach anywhere in Spain even in May--unless you just want to walk along the beach. But I'm not a beach person, and my assumption could be wrong.

Madrid, though farther south, will be a lot cooler than Barcelona at night in the spring; the difference in average low temperatures is about 9 degrees (F). I haven't dug deeply into weather patterns in northern and central Spain but would probably choose to head over there in late April or early May if I were traveling for just two weeks and not going to Andalucia. I like to use the website timeanddate.com to see actual, day-by-day, historical weather statistics for places I plan to visit. The data goes back about ten years, and I usually check the most recent five. I like to know how hot it could get in the daytime and how cold at night. In Spain the dinner hour begins around 9:30 or 10:00 PM, so you are likely to be more exposed than usual to nighttime temperatures when traveling there.

Barcelona weather April 2023

Madrid weather April 2023

Use the pull-down box above the graph at the right to change the month/year displayed.

Two weeks isn't long enough to cover the entire country, but it would be good for Barcelona, Madrid, and a few day trips--or perhaps a couple of nights in Toledo and/or Girona so you don't spend all your time in the two really large cities. You could also consider including Zaragoza, a very interesting, less touristy city located right on the rail line between Barcelona and Madrid.

Note that there are a number of sights in Barcelona that suffer from very, very long ticket lines. If you want to see them, you should buy tickets a bit ahead of time rather than showing up at these sights empty-handed:

La Sagrada Familia
Parc Guell
Casa Mila (La Pedrera)
Casa Batllo
Picasso Museum
Palau de la Musica Catalana (English tours can sell out)

Thank you for al the replies. Would we be better off going in September? Trying to avoid the heat but at the same time to be able to enjoy the beach a little.

There’s no way to avoid crowds in either of those cities. We tend to travel to Spain in either May or September.

Depends where you want to go weather September is different. Again, check the historical weather calendars.
I haven't been since 2017 but I was in sandals and skirt in late October in Granada and miss and hat in Madrid Nov 9.
North is considerably different to South in terms of weather. Somewhere, I read a trip report for September on the Basque coast. Comfortable temps. If you want beach, then stick to the coast near Barcelona.

Not sure I follow the logic of avoiding crowds in Spain but going to Barcelona and Madrid!! May is a wonderful time in Spain with the various spring fairs..EG Córdoba patio festival, Jerez horse fair, etc. Also biggest holiday in Madrid for San Isidro on May 15 in 2024. Lots of big crowds but worth it! If you want beach weather in May go to the Canaries!

For actually going into the water, I think September would surely be better.

But the days will be a lot longer in late April or May. That matters a great deal to me but perhaps not at all to you.

When I was in Spain, it was mid to late May. Madrid was having a heat wave. It was 40 degrees C (104 F), but Barcelona was cooler--not beach weather. We went to Nice after that, and it wasn't beach weather there, yet, either, in late May. I mean, one could sit on the beach on a couple of the days, but the water was cold.

Avoid Pascua de Resurrección.
There is no beach in Madrid.
I would do:
Madrid 6 days
Sitges 3 days, beach!
Barcelona 5 days

We were in Barcelona last September and it was plenty hot. It was the "heat wave" year and those temps had lowered but it was still plenty hot for me. Madrid was still warm, but bearable. I would go in May. As a matter of fact, I am going in May of next year!

Early May was IMO, really hot in southern Spain. I don’t know about water temps, but the sun was hella powerful

You will encounter a number of holidays in May - Labor Day, Pentecost, and what ever Saints days are being celebrated in small towns. Look them up so you are prepared.