I WANT TO CREATE A FORMULA USING DATES

A

armymatt

I have dates in 3 seperate colums for 3 seperate tasks on Sheet 1. On Sheet 2
with in the same file i have a colomn that has either nothing or an X in it
when a specific task is complete.

the three date colomn's are:U5, V5, W5.
the other column is H5 on the other sheet.

I want to create a formula that when all 4 variables are entered, it puts
the date when all tasks were completed in cell T5 on sheet one.

what i have come up with so far is this
=IF(AND(U5=dd-mmm-yy,AND(V5=dd-mmm-yy,AND(W5=dd-mmm-yy,AND('sheet2'!H5=X)))),"TODAY(dd-mmm-yy)",""


any help would be greatly appreciated
 
F

FSt1

hi,
try this....
=IF(AND(CELL("format",U5)="D4",CELL("format",V5)="D4",CELL("format",W5)="D4",Sheet2!H5="X"),TODAY(),"")

the format of =cell() may give you problems. per help on =Cell(), your
format = D1.
in a cell(off to the side) enter....=cell("format",U5). replace the return
format in the above formula with the format that =cell() returned.

regards
FSt1
 
F

FSt1

hi
don't use this formula. i just did another test and it failed.
working.

regards
FSt1
 
F

FSt1

hi
use this one instead....
=IF(AND(ISNUMBER(U5)=TRUE,ISNUMBER(V5)=TRUE,ISNUMBER(W5)=TRUE,Sheet2!H5="X"),TODAY(),"")

regards
FSt1
 
D

David Biddulph

.... which can, of course, be simplified to
=IF(AND(ISNUMBER(U5),ISNUMBER(V5),ISNUMBER(W5),Sheet2!H5="X"),TODAY(),"")
as you don't need the =TRUE in each case.

I wonder, however, whether the OP really wants TODAY() as the result, as the
answer will then change tomorrow. If he wants the value to freeze, he
probably needs a VBA solution.
 
A

armymatt

Hey I tried the first formula you said... and you're right I had problems but
I also did the "cell("format",U5)" in another cell and it came back with "G".
I plugged that into the formula as follows:


=IF(AND(CELL("format",U5)="G",CELL("format",V5)="G",CELL("format",W5)="G",'Warrior Task Tracker'!H5="X"),TODAY(),"")

and it worked as did the second formula you figured out. The only issue I
hope doesnt happen is that the date in the cell I put the formula doesnt
change, everytime I open the file. I want the date to reflect the day all
the other cells are filled.

Thanks for your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complex formula 3
Using cell contents within a formula 2
Incrementing dates in the format MMM-YY 3
Date Formula 2
Date formatting problem 3
Do I need to create 6 Reports 3
Sumproduct search between Dates 5
Formula to calculate someone's exact age 6

Top