Working with Dates in Excel

[manishseth]

On the heading of my weekly report I wanted the week start date "Saturday" and the end date "Friday" should show automatically when I open the sheet and should change with the date of the month automatically. I did not want to change it manually every week. I achieved the same by the below formula.

My Question: Is there any smarter way to get the same results?

Result: 21-07-18 to 28-07-18

Formula: =TEXT(IF(WEEKDAY(TODAY())=7,TODAY(),TODAY()-WEEKDAY(TODAY())),"DD-mm-YY")&" to "&TEXT(IF(WEEKDAY(TODAY())=7,TODAY()+(WEEKDAY(TODAY())),TODAY()+(6-WEEKDAY(TODAY()))),"DD-MM-YY")

 

[Norie]

Not much shorter.

=TEXT(TODAY()-MOD(WEEKDAY(TODAY()),7),"dd-mm-yy") &" to "&TEXT(TODAY()-MOD(WEEKDAY(TODAY()),7)+7, "dd-mm-yy")

 

[Tetra201]

A shorter formula:

=TEXT(INT(TODAY()/7)*7,"dd-mm-yy to ")&TEXT(INT(TODAY()/7)*7+6,"dd-mm-yy")

If your version of Excel has TEXTJOIN, here is an even shorter one:

=TEXTJOIN(" to ",,TEXT(INT(TODAY()/7)*7+{0,6},"dd-mm-yy"))