ExcelBanter - Change day of week to day of month

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Change day of week to day of month (https://www.excelbanter.com/excel-discussion-misc-queries/228869-change-day-week-day-month.html)

Change day of week to day of month

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

Change day of week to day of month

=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 wrote:

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

Change day of week to day of month

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" wrote:

=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 wrote:

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

Change day of week to day of month

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 wrote:

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" wrote:

=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 wrote:

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

--

Dave Peterson

Change day of week to day of month

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" wrote:

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 wrote:

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" wrote:

=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 wrote:

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

--

Dave Peterson

Change day of week to day of month

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

"Tony" wrote:

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" wrote:

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 wrote:

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" wrote:

=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 wrote:

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

--

Dave Peterson

Change day of week to day of month

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?

Tony wrote:

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" wrote:

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 wrote:

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" wrote:

=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 wrote:

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

--

Dave Peterson

--

Dave Peterson