Formula

[steveautsin99]

Hi

Can u help with a formula which would show the current week and previous week.
For us week starts from saturday and ends on Friday.

For ex the current week should show as 26-Feb to 4-Mar
the prvious week should show as 19-Feb to 25-Feb

Excatly in the same format.

Thanks in advance

 

[jimrward]

do you want to create a whole list of these, as you only need to create the first set and adjust by 7 days to generate the first set

did a bit of google and came up with the following, i am sure someone will come up with something shorter

in A1 type the following
=TODAY()+(WEEKDAY(TODAY())>=7)*7-WEEKDAY(TODAY())+7
in B1 type
=A1 + 7

in A2 type
=TODAY()-7+(WEEKDAY(TODAY()-7)>=7)*7-WEEKDAY(TODAY()-7)+7
in B2 type
=A2 + 7

format the cells accordingly to the date format you require

 

[steveautsin99]

It should show up as follows the current week and the precious week should be entirely in one single column


Yesterday
24-Feb-11


Current Week
02 October to 08 October


Previous Week
25 September to 01 October

 

[jimrward]

ok in single cell for current week try

=TEXT(TODAY()+(WEEKDAY(TODAY())>=7)*7-WEEKDAY(TODAY())+7,"dd mmm") & " to " & TEXT((TODAY()+(WEEKDAY(TODAY())>=7)*7-WEEKDAY(TODAY())+7)+6,"dd mmm")

and for the previous in next cell try

=TEXT(TODAY()-7+(WEEKDAY(TODAY()-7)>=7)*7-WEEKDAY(TODAY()-7)+7,"dd mmm") & " to " & TEXT((TODAY()-7+(WEEKDAY(TODAY()-7)>=7)*7-WEEKDAY(TODAY()-7)+7)+6,"dd mmm")

I am sure some bits will cancel out arithmetically but i will leave it as this for the moment