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.

 

[Jerry Sullivan]

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

Assuming that by format of 1/1/2011, you mean M/D/YYYY format and not D/M/YYYY, this formula should work in your sheet Invoice:

=TEXT(DATEVALUE(Roster!$A$1&" "&MATCH("o",Roster!A$4:AE$4)&", "&Roster!$A$2),"M/D/YYYY")

This is based on your description of the first few columns of sheet Roster looking like this:

<b>Roster</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:57px;" /><col style="width:26px;" /><col style="width:26px;" /><col style="width:26px;" /><col style="width:26px;" /><col style="width:26px;" /><col style="width:26px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:left; ">January</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:left; ">2011</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">3</td><td style="text-align:center; ">4</td><td style="text-align:center; ">5</td><td style="text-align:center; ">6</td><td style="text-align:center; ">7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td style="text-align:center; ">o</td><td > </td><td > </td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


As an alternative to using the TEXT function you could format the cell format to M/D/YYYY.

 

[Rivermark]

JS411,

That didn't work. Have a N/A error

 

[Jerry Sullivan]

The formula assumes the value of Cell A1 = January and
Cell A2=2011 (without quotes).

It would have an error if for example the value in A1 = 1/1/11 and you are using formatting to display January.

Also, what version of Excel are you using?

Please check to see if that is the difference.

 

[Rivermark]

Jerry,

It looks as if the "MATCH("o",Roster!A$4:AE$4)" part is giving me the problem. It is not returning the desired number of day of month. The "January" and the "2011" is working in the evaluation.

Thanks for your patience here.

 

[Rivermark]

Jerry,

It looks as if the "MATCH("o",Roster!A$4:AE$4)" part is giving me the problem. It is not returning the desired number of day of month. The "January" and the "2011" is working in the evaluation.

Thanks for your patience here.

Ah! I missed a piece of info for you, in row 4 in each column there is another letter in the cell or a blank. But, I am still looking for the one that has the "o" under it.

 

[Jerry Sullivan]

Ah! I missed a piece of info for you, in row 4 in each column there is another letter in the cell or a blank. But, I am still looking for the one that has the "o" under it.

If you enter just: =MATCH("o",Roster!A$4:AE$4)
do you get #N/A?

maybe your "o" isnt really an "o" but a symbol or something.

Try changing your symbol temporarily to "X"
and test for =MATCH("X",Roster!A$4:AE$4)

 

[Jerry Sullivan]

If you enter just: =MATCH("o",Roster!A$4:AE$4)
do you get #N/A?

maybe your "o" isnt really an "o" but a symbol or something.

Try changing your symbol temporarily to "X"
and test for =MATCH("X",Roster!A$4:AE$4)

You should add the match type parameter too....a 0 for exact match.
=MATCH("X",Roster!A$4:AE$4,0) <!-- / message --><!-- sig -->

 

[Rivermark]

You should add the match type parameter too....a 0 for exact match.
=MATCH("X",Roster!A$4:AE$4,0) <!-- / message --><!-- sig -->

Jerry,

That work's perfectly! You're awesome.

The last thing that I would like this formula to do is, if there is not a "o" in the row that it will return just a blank cell

 

[Jerry Sullivan]

The last thing that I would like this formula to do is, if there is not a "o" in the row that it will return just a blank cell

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"))