formula to return week commencing

**excellentexcel** · 01-23-2009, 03:22 AM

i would like for excel to recognise a date in cell a1
lets say today 23/01/09 and return the answer "w/c 19/01/09"
weeks start on a monday and dates input will only be between monday to friday
so any data inputted should look for and return the monday before as per previous example

kind regards

**VBA Noob** · 01-23-2009, 03:33 AM

Maybe

="W/C "&TEXT(A1-(WEEKDAY(A1)-2),"DD/MM/YY")

VBA Noob

**excellentexcel** · 01-23-2009, 03:45 AM

sorry vba
that is perfect but what i forgot to say was
that what i need is if a cell has a saturday or sunday date in it i need it to say something like "incorrect date that is a weekend"

**VBA Noob** · 01-23-2009, 03:53 AM

and dates input will only be between monday to friday

You specified it won't be a weekend

Use data validation to restrict user inputing a weekend

=WEEKDAY(A1,2)<=5

VBA Noob

**excellentexcel** · 01-23-2009, 03:58 AM

thanks vba
yes i did say dates would only be monday to friday
but thats if i enter them and if someone else uses it they might accidently put a weekend in.
but thanks very much that works