How do I get a formula in a cell to read from another cell

[Charles Stover]

Column D = Date closed (1/1/2009 format)
Column AF = Day Closed (custom "d" format)
Column AG = Week Closed (General Format)

Column AF gets the day closed using the formula:
=D2
Column AG gets the Week Closed using the formula:
=IF(OR($AF2=1,$AF2=2,$AF2=3,$AF2=4,$AF2=5,$AF2=6,$AF2=7),"Week One,""))

The formula works in Column AF, but I can't the formula to work in Column
AG.
It leaves the cells blank.
I thought maybe the formula in AF is interfering with the formula in AG.
So I took the fomula out of AF and put the same value in the cell. Presto.
The formula in AG worked.

Is there a solution to this? Why can't the formula read the value from the
cell unless I make it a "Number" format?

Maybe I am doing everything wrong. Any help would be appreciated.

 

[Elkar]

Excel stores dates as serial numbers. 1/1/2009 would be 39814 (the number of
days since January 1, 1900). When you apply a cell format (such as date) it
only affects what is displayed on the screen. It does not change the
underlying value in the cell. Formulas always use the underlying value of a
cell, and pay no attention to the displayed format. Thus, your formula
(IF(OR($AF2=1...etc) would not work because AF2 contains a serial number, not
the number 1 which is displayed.

Assuming that you define "Week 1" as being the first 7 days of a month, try
this formula in AF:

="Week"&CEILING(DAY(D2)/7,1)

Or, if you want 1,2,3... spelled out as "one","two","three" you could use:

="Week"&LOOKUP(CEILING(DAY(D2)/7,1),{1,2,3,4,5},{"One","Two","Three","Four","Five"})

HTH
Elkar

 

[Charles Stover]

I tried the ="Week"&CEILING(DAY(D2)/7,1)
It worked, but how do I get it to do "Week 1" or "Week 2", etc. through 31
days?