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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225

ADVERTISEMENT

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

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225

ADVERTISEMENT

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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
"...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

Well-known Member
Joined
Aug 21, 2009
Messages
584
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
If the month is a number . . .
Code:
=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.
Code:
=weekday(a2)
will return a code number for the day of the week, where 1 is Sunday.
You can elaborate on this . . .
Code:
=LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"})
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,147
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top