How do I return a number for a formatted cell value?

[Guest]

I have a table full of dates. The raw data in the cells consists of dates in
mm/dd/yyyy format. The visual data, modified by formatting the cells (right
click, format cells, date), is: ddd mm/dd/yy.

The first three characters in the formatting are the day of the week (Mon,
Tue, Wed, etc.) I want to reference that data (the ddd) and assign a number
to it.

Ex. Mon=500, Tue=350, Wed=10,000

Is there any way to reference something that is not hard data in a cell to
return a value in another cell. I can't seem to "grab" that data to return
my value.

All help is greatly appreciated! Thanks!

 

[Guest]

Since you're grabbing something that can be calculated from the hard data in
the cell, you should be able to get there. =weekday(a1) will return a
number, 1-7, corresponding to the day of the week. Then you could use a
choose or vlookup function to convert the weekday to the assigned number.

 

[Ron Rosenfeld]

I have a table full of dates. The raw data in the cells consists of dates in
mm/dd/yyyy format. The visual data, modified by formatting the cells (right
click, format cells, date), is: ddd mm/dd/yy.

The first three characters in the formatting are the day of the week (Mon,
Tue, Wed, etc.) I want to reference that data (the ddd) and assign a number
to it.

Ex. Mon=500, Tue=350, Wed=10,000

Is there any way to reference something that is not hard data in a cell to
return a value in another cell. I can't seem to "grab" that data to return
my value.

All help is greatly appreciated! Thanks!

You can use either the WEEKDAY function or a TEXT function. The former returns
a number (1-7 or 0-6); the latter a text string (e.g. Mon, ...)

=WEEKDAY(A1)

=TEXT(A1,"ddd")


--ron