Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Location Oriented Formula
In cell E4 I have the formula:
=IF(ROUNDUP(MONTH(D4)/3,0)=ROUNDUP(MONTH(TODAY())/3,0),EDATE(DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODA Y())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1) E4 also has two conditional formats: Condition 1 is Formula is =IU4=FALSE To format shading as White Condition 2 is Formula is =IU4=TRUE To format shading as Red In cell IU4 I have the formula: =OR(AND(TODAY()=DATE(YEAR(TODAY()),INDEX({3;6;9;12 },INT((MONTH(TODAY())+2)/3))+1,0),D4<DATE(YEAR(TODAY()),INDEX({3;6;9;12},IN T((MONTH(TODAY())+2)/3))-2,1)),D4<DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT(( MONTH(TODAY())+2)/3))-5,1)) Cell IU4 has no conditional formatting When I enter the 25 Mar 09 in cell D4 cell E4 reads 30 Sep 09 with red shading, like it should. When I enter 25 May 09 in cell D4 cell E4 reads 30 Sep 09 with white shading, like it should. When I enter 25 Aug 09 in cell D4 cell E4 reads 31 Dec 09 with white shading, like it should. However, at work with the same formula in cell E4 on my memory stick the formula shows up in the formula bar as: =IF(ROUNDUP(MONTH(D4)/3,0)=ROUNDUP(MONTH(TODAY())/3,0),'C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\Analysis\ATPVBAEN.XLA'!EDA TE(DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1) With #NAME? in cell E4. How can I get this formula to display at work the way it shows up in my hotel room? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Location Oriented Formula
Have you installed the Analysis tool pack on your work PC?
Hope this helps. Pete On Sep 23, 12:38*am, Loadmaster wrote: In cell E4 I have the formula: =IF(ROUNDUP(MONTH(D4)/3,0)=ROUNDUP(MONTH(TODAY())/3,0),EDATE(DATE(YEAR(TODA*Y()),3*ROUNDUP(MONTH(TOD AY())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(M*ONTH(TODAY())/3,0)+1,1)-1) E4 also has two conditional formats: Condition 1 is Formula is =IU4=FALSE To format shading as White Condition 2 is Formula is =IU4=TRUE To format shading as Red In cell IU4 I have the formula: =OR(AND(TODAY()=DATE(YEAR(TODAY()),INDEX({3;6;9;12 },INT((MONTH(TODAY())+2)/*3))+1,0),D4<DATE(YEAR(TODAY()),INDEX({3;6;9;12},I NT((MONTH(TODAY())+2)/3))-*2,1)),D4<DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT( (MONTH(TODAY())+2)/3))-5,1*)) Cell IU4 has no conditional formatting When I enter the 25 Mar 09 in cell D4 cell E4 reads 30 Sep 09 with red shading, like it should. When I enter 25 May 09 in cell D4 cell E4 reads 30 Sep 09 with white shading, like it should. When I enter 25 Aug 09 in cell D4 cell E4 reads 31 Dec 09 with white shading, like it should. However, at work with the same formula in cell E4 on my memory stick the formula shows up in the formula bar as: =IF(ROUNDUP(MONTH(D4)/3,0)=ROUNDUP(MONTH(TODAY())/3,0),'C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\Analysis\ATPVBAEN.XLA'!EDA TE(DATE(YEAR(TODAY()),3*R*OUNDUP(MONTH(TODAY())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TOD*AY())/3,0)+1,1)-1) With #NAME? in cell E4. How can I get this formula to display at work the way it shows up in my hotel room? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Bar show the location path file plus the formula | Excel Discussion (Misc queries) | |||
Macro - save to current location vs excel default location | Excel Discussion (Misc queries) | |||
Oriented datapoint markers on chart | Charts and Charting in Excel | |||
Working with "record-oriented" spreadsheets | Excel Discussion (Misc queries) | |||
Protected Row-Oriented Data Entry | Excel Worksheet Functions |