Forum Discussion
Solved
Start and End dates in same cell
Is there a way to display the start and end date of weeks in same cell (example: Jan 3 - Jan 9)
| Week 1 | Week 2 | Week 3 | Week 4 | Week 5 |
| Jan 3 - Jan 9 | Jan 10 - Jan 16 | Jan 17 - Jan 23 | Jan 24 - Jan 30 | Jan 31 - Feb 6 |
Is there a way to do this with a formula rather than write them all out?
See the attached sample workbook.
Enter the year in B1.
The weeks are actually the week numbers 1, 2, ..., with the custom number format "Week "0
The formulas in the cells below use these week numbers.
2 Replies
Depends on which Excel you are. As variant
=LET( year, 2022, weekNumber, SEQUENCE(,52), weeks, "Week " & weekNumber, start, DATE(year, 1, -2) - WEEKDAY(DATE(year, 1, 3)) +weekNumber * 7, end, start+7, IF( {1;0}, weeks, TEXT(start, "mmm d") & " -" & CHAR(10) & TEXT(end, "mmm d") ) )which generate entire sequence
To show Start and End date as above don't forget to apply Wrap text to cells
See the attached sample workbook.
Enter the year in B1.
The weeks are actually the week numbers 1, 2, ..., with the custom number format "Week "0
The formulas in the cells below use these week numbers.
