Sticky: Think you're good? Crack this one...

[ClimoC]

h

 

[Tom Urtis]

The text thing is only in cell B3, but we can ignore that for a moment.

Keep cell A3 as it is
In cells A4 enter the formula
=A3 + 1
and copy down to cell A33.

You can do the same to cell B3 (same formula as A3, formatted as general), and the same formulas in B4:B33. Then, for range B3:B33, custom format as
DDDD
and they will look like weekdays in column B but still be dates as their underlying value and will work in your calculations.

 

[pgc01]

Hi ClimoC

Try in A3:

=DATE(A1,IF(ISNUMBER(-B1),B1,MONTH("1 "&B1)),1)

Will work for B1 number, and both for the first 3 letters of the month (Dec) and for the full name (December) (and in between)

Tom:

It's an upside down Greek letter Mu in lower case

No, it's really an inverted h :

http://www.fileformat.info/info/unicode/char/0265/index.htm

 

[ClimoC]

Cool! Thanks Higgs!

I wouldn't worry too much about the Month as text bit. What I will do is have a data validation list in a1 which has the month as text, and then have b1 be a vlookup to a hidden data sheet I have which finds the corresponding number value for that month, then have the calculations run off that.

Thanks again!

 

[Gerald Higgins]

Cool! Thanks Higgs!

Do you mean me ?

You're welcome, but I think Tom Urtis's suggestion of formating the date as DDDD is better than my suggestion of a lookup formula for the weekday.