WEEK Commencing

Hi i currently use this forumula to display the week number

="WK "&MIN(WEEKNUM(A2),52)&" "&YEAR(A2)

How do I change it to show week comencing WC 12/02/17 or even WK 5 Commencing 12/02/17

Kind regards

Try this to display the W/C date for this week based on Sunday as the start of the week:


Displaying it with text as you request does mean that you can't simply add 7 to the last date to get the next one. If you were to (eg) put "W/C" on the line above then you can.

Sunday is the end of the week need it to start from Monday.

Also trying to do a conditional formate using manual rule entry that says if D10 is greater than C10

Like this?

=IF(D10>C10,"W/C "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()-WEEKDAY(TODAY())+2)),"DD/MM/YYYY"),"")

Hi Thanks

Sorry need to explain myself better

Forumula 1. ="W/C "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()-WEEKDAY(TODAY())+1)),"DD/MM/YYYY") beginning of the week is Monday end of the week is Sunday so need it to run from Monday .
Forumula 2 Iam using the conditional formation apply forumula to determin which cells I need it to say if D10 >e10

jonespandrew said:

Sorry need to explain myself better

Click to expand...

Yes, you do!

Forumula 1. ="W/C "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()-WEEKDAY(TODAY())+1)),"DD/MM/YYYY") beginning of the week is Monday end of the week is Sunday so need it to run from Monday .

Click to expand...

Already answered in Post #4. I started the week on Sunday because in your first post you said you wanted to show W/C 12/02/17, (which is Sunday). For Monday amend +1 to +2 in above formula as shown in post #4

Forumula 2 Iam using the conditional formation apply forumula to determin which cells I need it to say if D10 >e10

Click to expand...

Highlight the cells that you want the CF to apply to; Click Conditional Formatting on the Home Ribbon; Click New Rule at the drop-down; Click Rule Type "Use a formula.........."
In the Formula box type =$D$10>$E$10 then Click Format and select the particular format you want to use; Click OK; Click OK.
Excel will apply the format you have chosen to all the highlighted cells if E10 > D10.