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

[ClimoC]

h

 

[Tom Urtis]

I know ! I know !
It's the letter h.

 

[Special-K99]

No, no, it's a giraffe looking at an aeroplane

 

[pgc01]

ɥ

 

[Tom Urtis]

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

 

[ClimoC]

lol... let me try that without getting timed out...

What I need is for the following:

Based on two cells (call them A1 and B1) which will have a year (ie 2009) and a month (either as a number like '5' for May or just the word 'May')

I need something that will, in a cell in row 3 (call it 'A3'), put '01/05/09' there, but in proper excel date format so that calculations in the same column as this pasted date can be done (they will be calculations such as if(S19=<A3,then do this, otherwise do this) and so on.

Once I've got that, I can just fill the rest of the row with (cell to the left +1)

If someone could be really clever though, It'd be good to be able to in row 2: have what day of the week that date is

And if it stops autofilling the row with dates at the end of the month, that'd be great (so when it's a 30day month like June, the last column doesn't come up with 01/07/09)

PS - I cannot install any additional Controls like Calendars (don't even have the standard one built in which is weird)

Good luck - and thanks in advance!

 

[Tom Urtis]

lol... let me try that without getting timed out...

Ha -- how long did it take you to type the letter h ? Couldn't resist.

OK, more serious...

is this what you need in A3?
=DATE(A1,B1,1)

and this in B3?
=TEXT(A3,"dddd")

 

[Special-K99]

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

No it's a dead giraffe, it spent too long looking at that aeroplane

 

[ClimoC]

Tried the =TEXT options, but they couldn't return the values as date format, so subsequent calculations would never work because it's trying to ask if the text "01/05/09" is equal or less than the (number)date"01/05/09" - so of course it returns an error.

So basically, depending on what month and year you select in A1 and B1, to fill row 3 with all the dates for that calendar month.

 

[Gerald Higgins]

If the month is a number . . .

=date(a1,b1,1)

If the month is text, this might be more complicated.

For day of the week, let's assume the date has been put into A2.

=weekday(a2)

will return a code number for the day of the week, where 1 is Sunday.
You can elaborate on this . . .

=LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"})