Help with If statements and date functions.

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
<Edit> Hello, </Edit>

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.

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
Well, I think I didnt explain what I'm trying to do correctly...basically the set up you described is correct, but the X's would be put in manually, once that months work had been complete. What I was hoping, was to develop an If statement which basically would check to make sure that for the dates that were before today, and had an X marked in the cell below it, it would come back with some form of true value. If the date was before today, but had no X under it, it would come back as false.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
Would it be possible to have all of this in one cell, lets say A3, and have A3 represent whether the X is marked under everydate that falls prior to the present day?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
if all you want to do is highlight the column A, you can use conditional format as Venkat mentioned before

=OR(AND(C2<TODAY(), C3<>"X"),AND(D2<TODAY(), D3<>"X"),AND(E2<TODAY(), E3<>"X"),AND(F2<TODAY(), F3<>"X"),AND(G2<TODAY(), G3<>"X"),AND(H2<TODAY(), H3<>"X"),AND(I2<TODAY(), I3<>"X"),AND(J2<TODAY(), J3<>"X"),AND(K2<TODAY(), K3<>"X"),AND(L2<TODAY(), L3<>"X"),AND(M2<TODAY(), M3<>"X"),AND(N2<TODAY(), N3<>"X"))

Works for me in xl07
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
Yes this can get difficult because it would involve reworking the formula's at the end of the month. Is it possible to have something described above set up, but have excel just automatically check the date and verify there is an X under it, if the date had not passed?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.