Help with If statements and date functions.Closed

Question

Hello, I am trying to put a spreadsheet whose purpose will be to keep track whether certain updates on seperate funds has taken place. I want the spreadsheet to start as of 12/31/2008 and run through the present on a monthly basis. Basically what I want excel to do for me is to look at the dates which are in Row 2 between Column C and N and identify that the date is before today, and then go down to Row 3 and make sure the Row is marked with an X ( an indication that its been done). If it finds this to be true i want it to do nothing, if it finds it to be false, I was hoping it could highlight Column A yellow. I've been tryng numerous If & And statements but to no avail. Any help would be greatly appreciated.

venkat1926 · Answer

suppose the data is something like this from A1 down and right 
the data is columns C to H in row no. 2
							
		02/13/10	07/08/09	05/19/10	03/17/10	01/17/09	02/09/09
		X	X		X	X	X
in C3 type this formla 
=IF(C2<TODAY(),"X","")
copy C3 to D3,E3,F3,G3,H3
You will get x's as in row no. 3 above 

your coloring conditions are not clear. I suppose you mean that dates which are beyond today should be marked yellow(in row no. 2)
for this use conditional formatting 
select C2
click format(menu)-conditional formatting 
in the "conditional formatting window that comes up 
under conditon1 choose "formula is"
in the next small window to the right type this formula 
=c2>=today()
(both the two equal to signs are necessary)
now click "format" in the second row of the same window(in the second row)
at the top there are three choices choose "pattern"
the color window comes up
you will get many colors choose yellow
click ok (in the color window)
again click ok in the conditional formatting window 
obviously c2 will NOT be colored 
select  C2 click edit-copy and highlight D2 to H2 and click 
edit-pastespecial-choose format (only format)
click ok
now E2 and only E2 will be marked yellow

if this is successful in this sample sheet use it in your file 

KEEP THE ORIGINAL FILE SAFELY SOMEWHERE SO THAT IT CAN BE RETRIEVED IF THERE IS A MESS UP.

venkat1926 · Answer

in C4 type this formula 

=IF(C2<TODAY(),IF(C3="x","true","false"),"")
copy c4 to D4 to to the of the row where data is there. 
will this help you.

venkat1926 · Answer

theoretically possible but may be complicated which it may be difficult adapt at a different situation.  Besides you can have  only seven nested IFs in excel 2002 or 2003.think about this and revert back if necessary

venkat1926 · Answer

It is possible to run a macro on opening the file on a particular day.  
 open vb editor.  control+R in the project window go to your file if the sheet names and"this workbook" is seen it is ok. otherwise click the +sign left of "microsoft excel objecta"under your file name.  
double click "thiswokbook" and on the right side you get a wndow  
in the right hand side window but on the left small window click the arrow and choose "workbook". immediately a event code format opens like this  
Private Sub Workbook_Open() 

End Sub 
in between type this code  

if day(date)+1=1 then 
<name of the macro> 
end if 

I have not tested this now. try this and if there is problem indicate clearly what the problem is.

This event code will run the macro as soon as you open the file on the last date of the month.

Help with If statements and date functions.

4 responses

Similar discussions

Subscribe To Our Newsletter!