Day Date Problem

  • Thread starter Thread starter PeteB
  • Start date Start date
P

PeteB

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.
 
Thanks very much for that - works perfectly. A helluva lot more straight
forward than my way.
 
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


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.
 
Back
Top