Returning a complicated date formula

[Rivermark]

Here is my dilemma,

I need a date returned for a set of certain criteria:

In a cell in worksheet "invoice" I need this date based off of data in worksheet "roster".
A row in worksheet "roster" contains a single text character in 31 consecutive columns with headers labled 1-31
If a cell contains an "o" I need the header number it falls under to correspond with the month and year this billing cycle falls under, which is listed in cells above the headers. (ex. January is entered in A1 and 2011 is entered in A2)
I also need the month that is entered in A1 to return its number instead of it's name (ex. January=1, February=2, March=3,...)

I need the cell in worksheet "invoice" to read in the format of 1/1/2011 based on the above criteria.

Any help or direction would be appreciated.

 

[Rivermark]

If you have xl2007, you can use the IFERROR function...
=IFERROR(TEXT(DATEVALUE(Roster!$A$1&" "&MATCH("o",Roster!A$4:AE$4,0)&", "&Roster!$A$2),"M/D/YYYY"),"")

For xl2003, I think this will work...
=IF(ISNA(TEXT(DATEVALUE(Roster!$A$1&" "&MATCH("o",Roster!A$4:AE$4,0)&", "&Roster!$A$2),"M/D/YYYY")),"",TEXT(DATEVALUE(Roster!$A$1&" "&MATCH("o",Roster!A$4:AE$4,0)&", "&Roster!$A$2),"M/D/YYYY"))

Jerry,

You've been the biggest help here. That works flawlessly. Thanks a ton!

I do have another problem to figure out. I'd love to use your brain if you have the time? Let me know.

 

[Jerry Sullivan]

Jerry,

You've been the biggest help here. That works flawlessly. Thanks a ton!

I do have another problem to figure out. I'd love to use your brain if you have the time? Let me know.

I'm very glad to have helped, and thanks for the kind feedback.

If you have another question, please just start another thread. There are so many great people on this board,
odds are someone will respond right away.