How can I set Microsoft Excel to display the day by inputting the date?

for example.

I type "20 Feb 07", and excel formatting auto changes that to "Tuesday 20 Feb 07"

I actualy need this for a more complex formula...

if A1= any date from monday to sunday of one week, then A2= mondays date+21

the only way i can comprehend this is to make a formula that says if A1= a monday A2=date+21, A1= a tuesday A2=date+20, etc.

any thoughts?

Cheers, Alex

Thanks Carim,

I didn't realise I could just type it in myself like that if it wasnt on the list.

Any thought on how i can do the other bit? Now i know its possible to auto set the day from the date, it must be possible for me to work from that to set another cell as monday+21 tuesday+20 wednesday+19 etc.

Format both cells to dddd dd mmm yy, then in cell A2, use:

=A1 + 23 - WEEKDAY(A1, 2)

Fantastic!

This is so much simpler than i expected.

it works brilliantly thanks to both of you.

While you are making my life so much easier, could I trouble you with an extra complication?

this time i wont simplify the case at all:

I am writing a sheet that has information about a food products.

each time a new sheet is issued the date will be changed, eg. sunday 18th feb 2007. the formula you gave allows me to calculate the best before date automatically. for the product i am working on, that best before date is 62 days from the production date.

however, very awkwardly there is a shift to take into accoutn aswell.

eg. sunday 18th night shift to sunday 25th day shift is one week, so i need to account for that. the only overlap is on the sunday, where sunday day is part of the previous week, sunday night is part of the next week.

the shift wil be selected from a drop down list, DAY or NIGHT, being the only options. how would i figure this into it?

Try this (assuming the "drop-down list" is using data validation, rather than a combo box). This will calculate the Monday that is 62 days (or less) from the product date. This will also assume that Sunday night shift is the next day. This also assumes that the drop-down list is in cell B1.

=A1+64-WEEKDAY(A1,2)+IF(AND(WEEKDAY(A1,2)=7,B1="Night"),7,0)

I believe this is what you are asking. If not, let us know.