Showing a date range in a cell

Thought of another way that does not require a starting date cell.

In B2, type the first range Jul 12 - Jul 18

B3 =TEXTBEFORE(B2," -")+7&" - "&TEXTBEFORE(B2," -")+13

fill down from there.

Doubtful this is exportable to Excel but it works in Numbers.

If you want the date ranges to be something other than 7 days, the formula can calculate the number of days in the range.

Type the first range into B2, same as before. Those two dates will determine the number of days.

B3 =TEXTAFTER(B2,"- ")+1&" - "&TEXTAFTER(B2,"- ")+1+STRIPDURATION(TEXTAFTER(B2,"- ")−TEXTBEFORE(B2," -"))

In general one date per cell is most efficient.

But if you need to display a beginning and end date in one cell then you can do something like this:

In C1, filled down:

=A1&" - "&B1

The result is just text. It cannot be used in further calculations.

SG

You could do it with a single cell that has the start date, and a column of formulas. Example below:

Formula in B2 =B$1+(ROW()−2)×7&" - "&B$1−1+(ROW()−1)×7

Fill down to complete the column (I only filled a few rows)

It uses the row number via the ROW function to determine how many 7's to add to the start date. My list started on row 2 so I had to subtract 2 for the left side date and 1 for the right side date.

The start date can be somewhere else, even on another sheet.

Hi ReddishOne,

Welcome to the Numbers forum in Apple Support Communities! Your reply to Badunit:

ReddishOne wrote:

Thank you, this is just what I needed

Etiquette in this forum is to acknowledge a reply as Helpful (Gold Star) or Best Answer (Green Tick). Such actions encourage the author of a reply and declare the usefulness to others who may look for help on a similar problem.

Regards,

Ian.

Just remember that the combined cell won't be recognized as dates anymore.

Keep your A and B there to zoo any tests or calculations on