how to break a week between months

You can utilize some date manipulation and some case statements:

CONCAT(CASE WHEN DAYOFMONTH(`End Date`) < DAYOFMONTH(`Start Date`) THEN `End Date` - INTERVAL (DAYOFMONTH(`End Date`) -1) DAY ELSE `Start Date` END, ' - ', `End Date`

Breaking this down:

CONCAT - Joining the two dates together with a ' - ' between

CASE WHEN DAYOFMONTH(`End Date`) < DAYOFMONTH(`Start Date`)

Is the end day of the month less than the start date's day of the month. In other words. Did we cross month boundaries?

`End Date` - INTERVAL (DAYOFMONTH(`End Date`) -1)

Take the end date and subtract the number of days for the given day of the month. -1 here to make sure we don't go to the end of the prior month but to the first of the month (we want day 1 not day 0).

@WorldWarHulk Here is an alt approach - I used Magic2 for this example:

Note that my Date column is labelled Date && my week starts on Sunday ends on Saturday.

Also, it assumes that crossing months defaults to the first day of the later month - thus 10/1-10/1 and NOT 9/25-9/30 ( the week would be defined as 9/25->10/1 you can handle this in the case statement by checking whether the number of days is greater in the prior month vs later month for Cross Month conditions and select which label you want to apply).

Using A formula Tile:

FirstDayOfWeek: date_add(`Date`, interval -WEEKDAY(`Date`)-1 day)

LastDayOfWeek:  date_add(date_add(`Date`, interval -WEEKDAY(`Date`)-1 day), interval 6 day)

Cross Month Boundaries: case when MONTH(`FirstDayOfWeek`) = MONTH(`LastDayOfWeek`) then 0 else 1 end

Label:

case

   when `Cross Month Boundaries` = 0

   then CONCAT(`FirstDayOfWeek`, '-', `LastDayOfWeek`)

   else CONCAT(DATE_SUB(`LastDayOfWeek`, INTERVAL DAYOFMONTH(`LastDayOfWeek`)-1 DAY), '-', `LastDayOfWeek`) 

end

Hope this helps!