Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I return a number for a formatted cell value?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I return a number for a formatted cell value?
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. "gmbhbls" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I return a number for a formatted cell value?
On Mon, 14 Nov 2005 17:38:04 -0800, "gmbhbls"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying the number of a custom formatted number cell | Excel Discussion (Misc queries) | |||
number formatted cell | Excel Discussion (Misc queries) | |||
The number in this cell is formatted as text or preceded by an apostrophe | Excel Programming | |||
How to make a cell return the formatted value in a text string (i. | Excel Worksheet Functions | |||
Function to return colour of formatted cell | Excel Worksheet Functions |