I am creating a spreadsheet to be used every month to track work. I don't even know if this is possible. In cell F1 I have the current month and year entered using date formatting (April-11) I typed it in as 01/04/11 so it would work though. I am hoping the first two columns of my sheet can formulate automatically based on the date entered in F1 to eliminate time spent entering it every month...

Starting on row 7 I would like the following table (Headings are in row 6):

To start the list, B7 will show the first day of the month that does NOT fall on a Sunday

Column A will calculate (and show in text) the day of the week determined in column B (i.e. Friday)

Column B will show the day of the month (1, 2, 3..., 30, 31).

The "catch" is that Sundays are never to be listed. For the month of April, cell A7 should show Friday...cell A8 should show Saturday... Cell A9 should show Monday.....

Cell B7 should show 1, cell B8 should show 2, cell B 9 should show 4I hope I've clarified what it is I'm looking for. If this is possible, I am confident that it is one of you that will know how.

I appreciate any help with this.

You are going to need several formulas for this to work,

they are long and must be placed in the correct cells.With the Start date in cell F1:

In Cell B7 enter the formula:=IF(WEEKDAY(F1,1)=1,F1+1,F1)

In Cell A7 enter the formula:

=CHOOSE(WEEKDAY(B7,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

We now change gears, and formulas:In Cell B8 enter the formula:

=IF(MONTH(B7+1)=MONTH($F$1),IF(WEEKDAY(B7+1,1)=1,B7+2,B7+1),"")

Drag down to row number 33.

In Cell A8 enter the formula:=IF(ISERROR(CHOOSE(WEEKDAY(B8,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")),"",CHOOSE(WEEKDAY(B8,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"))

Drag down to row number 33

See how that works.

You may want to copy and past the last formula, it is a bit long.EDIT ADDED:

Forgot, you must custom format your cells in column B as "DD", that way they will show only the Day.

MIKE

That worked perfectly! Thank you so much for your help with this. Anita

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History