I'm trying to set up a spreadsheet which when I input a date in one column (B), it will automatically fill in the day of the week in the next (C).

I've used the formula:
=IF(WEEKDAY(B9)=1,"Sun",IF(WEEKDAY(B9)=2,"Mon",IF(WEEKDAY(B9)=3,"Tues",IF(WEEKDAY(B9)=4,"Wed",IF(WEEKDAY(B9)=5,"Thurs",IF(WEEKDAY(B9)=6,"Fri",IF(WEEKDAY(B9)=7,"Sat","")))))))

This works ok when CELL B9 is filled with a date but when B9 is empty it returns 'Sat'.

How can I adjust this so that C9 would remain blank if B9 is blank.

Any help would be appreciated because this is starting to drive me up the wall.

--
PeteB
http://www.pw9.co.uk
**Remove the obvious from the email address**

Thanks very much for that - works perfectly. A helluva lot more straight
forward than my way.
--
PeteB
http://www.pw9.co.uk
**Remove the obvious from the email address**
"J.E. McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> One way:
>
> C9: =IF(B9="", "", B9)
>
> format C9, using Format/Cells/Number/Custom, and enter "ddd"
> (without quotes) in the text box.
>
> In article <uVXLa.18486$(E-Mail Removed)>,
> "PeteB" <(E-Mail Removed)> wrote:
>
> > I'm trying to set up a spreadsheet which when I input a date in one
column
> > (B), it will automatically fill in the day of the week in the next (C).
> >
> > I've used the formula:
> >
=IF(WEEKDAY(B9)=1,"Sun",IF(WEEKDAY(B9)=2,"Mon",IF(WEEKDAY(B9)=3,"Tues",IF(WE
EK
> >
DAY(B9)=4,"Wed",IF(WEEKDAY(B9)=5,"Thurs",IF(WEEKDAY(B9)=6,"Fri",IF(WEEKDAY(B
9)
> > =7,"Sat","")))))))
> >
> > This works ok when CELL B9 is filled with a date but when B9 is empty it
> > returns 'Sat'.
> >
> > How can I adjust this so that C9 would remain blank if B9 is blank.
> >
> > Any help would be appreciated because this is starting to drive me up
the
> > wall.

A solution I use is as follows:

It involves a very simple "IF" formula coupled with the "ISBLANK" formula, the WEEKDAY formula and a simple "VLOOKUP" formula. If you're not familiar with vlookup have a go anyway because vlookup is v useful.

In my example the date is input in column B and the day is displayed in the adjacent cell in column A...
Firstly set up the vlookup cells somewhere out of the way. Here's an example:

1 (in cell Y1) Monday (in cell Z1)
2 (in cell Y2) Tuesday (in cell Z2)
3 Wednesday
4 Thursday

and so on up to

7 (in cell Y7) Sunday (in cell Z7)

Next... in column A enter the formula:

=IF(ISBLANK(B1),"",VLOOKUP(WEEKDAY(B1,2),$Y$1:$Z$7,2))

Copy this down the column as far as you want to go and then, whenever you enter a date in column B the adjacent cell in column A will display the actual day.

Hope this helps... if it's tricky post a reply here and I'll send you a small spreadsheet with it on

Dave G


"PeteB" <(E-Mail Removed)> wrote in message news:uVXLa.18486$(E-Mail Removed)...
I'm trying to set up a spreadsheet which when I input a date in one column (B), it will automatically fill in the day of the week in the next (C).

I've used the formula:
=IF(WEEKDAY(B9)=1,"Sun",IF(WEEKDAY(B9)=2,"Mon",IF(WEEKDAY(B9)=3,"Tues",IF(WEEKDAY(B9)=4,"Wed",IF(WEEKDAY(B9)=5,"Thurs",IF(WEEKDAY(B9)=6,"Fri",IF(WEEKDAY(B9)=7,"Sat","")))))))

This works ok when CELL B9 is filled with a date but when B9 is empty it returns 'Sat'.

How can I adjust this so that C9 would remain blank if B9 is blank.

Any help would be appreciated because this is starting to drive me up the wall.

--
PeteB
http://www.pw9.co.uk
**Remove the obvious from the email address**