Previous Topic Next Topic

Help with If statements and date functions.

Closed
Lax97 - Mar 22, 2010 at 02:53 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 29, 2010 at 08:39 PM
<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 responses

Answer 1 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 22, 2010 at 09:23 PM
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.
0
Lax97
Mar 23, 2010 at 07:46 AM
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.
0
Answer 2 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 23, 2010 at 10:24 PM
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.
0
Lax97
Mar 25, 2010 at 07:47 AM
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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 25, 2010 at 08:45 AM
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
0
Answer 3 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 25, 2010 at 08:22 PM
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
0
Lax97
Mar 29, 2010 at 07:39 AM
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?
0
Answer 4 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 29, 2010 at 08:39 PM
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.
0

Similar discussions

If cell A1 has text then cell B2 has today's Date
Sasha -
ac3mark -

1 response