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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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.
 
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!
Click to expand...


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
 
Back
Top