Change day of week to day of month

[Tony]

I have a row that contains days of the month - cell I3(DD). I want to read
the cell and change to to the day of the month - cell AE1(MM/DD/YYYY) using
another cell that has the first day of the month entered - cell
I5(MM/DD/YYYY). The reason I have to do this is because I will have to use
the I5 cell data to do a vlookup afterwards.

cell I3 = 2
cell AE1 = 04/01/2009
cell I5 = 04/02/2009

 

[Dave Peterson]

=date(year(ae1),month(ae1),i3)

or just incorporate that into your =vlookup():

=vlookup(date(year(ae1),month(ae1),i3), sheet2!a:e,3,false)

 

[Tony]

Dave, I am tryign to use the following code to enter my formula in a cell but
keep receiving a Error 1004. IIs there anywahere I cna look up how to code
these formula statements?


Application.Goto Reference:="r5C6"
wsPh.Range("f5").Formula = "=IF(F" & 3 &
"<>""DATE(YEAR($AE$1),MONTH($AE$1)"",F" & 3 & "),"""")"
Application.Goto Reference:="r5C7"
wsPh.Range("g5").Formula = "=IF(G" & 3 &
"<>""DATE(YEAR($AE$1),MONTH($AE$1)"",G" & 3 & "),"""")"
etc....

 

[Dave Peterson]

It looks like the formulas could be:

wsPh.Range("f5").Formula _
= "=IF(F3<>"""",DATE(YEAR($AE$1),MONTH($AE$1),F3),"""")"
wsPh.Range("g5").Formula _
= "=IF(G3<>"""",DATE(YEAR($AE$1),MONTH($AE$1),G3),"""")"

(you can drop the .goto stuff)

ps. I'm not quite sure what etc means, but if you're filling a range with the
same formula, you could use:

wsPh.Range("f5:H5").Formula _
= "=IF(F3<>"""",DATE(YEAR($AE$1),MONTH($AE$1),F3),"""")"

This is equivalent of selecting F5:H5, typing the formula for F5, but hitting
ctrl-enter to fill the selection with the formula. Excel will adjust the
formula just like it does when you copy|paste.

 

[Tony]

That works for the formulas. What if I want to set a time format for the
cell. This doesn't work, but i know I cam close:


wsPh.Range("f5:AD5").Formula = "=text("f5:AD5",""hh:mm
AM/PM"") "

 

[Tony]

Please disregard my last post. once I realized that I was putting in the
wrong format type I was able to solve it myself.

That works for the formulas. What if I want to set a time format for the
cell. This doesn't work, but i know I cam close:


wsPh.Range("f5:AD5").Formula = "=text("f5:AD5",""hh:mm
AM/PM"") "

 

[Dave Peterson]

When you're doing it manually, you write the formula for the cell that's active
in the selection.

Same in code:

wsPh.Range("f5:AD5").Formula = "=text(f3,""hh:mm AM/PM"")"

Notice that I changed your formula to point at F3 (and get adjusted for each
cell in F5:AD5).

You don't want a formula that refers to the cell that holds the formula.

========
Maybe you just want to format the cells nicely:

with wsPh.Range("f5:AD5")
.numberformat = "hh:mm AM/PM"
.formula = "=IF(F3<>"""",DATE(YEAR($AE$1),MONTH($AE$1),F3),"""")"
end with

But this doesn't make sense to me. Your formula either returns an empty string
"" or something that looks like a date. Why use HH:MM AM/PM as the format?

That works for the formulas. What if I want to set a time format for the
cell. This doesn't work, but i know I cam close:

wsPh.Range("f5:AD5").Formula = "=text("f5:AD5",""hh:mm
AM/PM"") "