calculates the weeks between weeks.

Why do you need VBA to return what a simple Excel function will provide. Even if you want VBA then it's usually simpler just to get VBA to add the Excel function.

But you need to give some specific examples. It's not clear what you mean by 'the date is in a cell' - what date and what cell, and 'the new week starts at 'Monday', and 'the new week starts at the date in the cell' - what cell and what's the value in the cell.

Upload a workbook with some data and clearly identify the values and cells you start with, and manually add the results you expect clearly identofying the results.

...sorry but you're going to have to give me some examples of what your data looks like and what resuts you expect and where you expect to see them.

If you can't upload the actual workbook - and I'm not sure why not, then mock up a simple example. I've no idea what you're line of code is attempting to do in the absence of your workbook.

You've not said why you need a macro when standard Excel functionality will give you what you want.