| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Day, Week or Month | Excel Discussion (Misc queries) | |||
| Name The Week Of The Month | Excel Discussion (Misc queries) | |||
| Week of the Month | Excel Discussion (Misc queries) | |||
| Count Week Of Month | Excel Worksheet Functions | |||
| How do I change from a 7 day week to a 5 day week in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
